EN
MySQL - SELECT query with GROUP BY and LIMIT
5 points
In this shot article, we would like to show how to solve the problem when we want to use SELECT query with GROUP BY and LIMIT in MySQL.
Quick steps:
- use nested query to number rows, e.g. article is here,
- then wrap the query using
WHEREclause to find rows on proper page.
Quick solution:
xxxxxxxxxx1
SELECT d.*2
FROM (3
SELECT -- nested query4
@number := @number + 1 AS `number`, -- nested query (with assigned row number)5
h.*, -- nested query6
COUNT(h.`name`) AS `count` -- nested query7
FROM (SELECT @number := 0) n, `hashtags` h -- nested query (with initialised `number` variable)8
GROUP BY h.`name` -- nested query9
) d10
WHERE d.`number` >= 10 AND d.`number` <= 15 -- WHERE clause used to find rows on proper pageWhere it is assumed:
- page has 5 rows (
d.`number` >= 10 AND d.`number` <= 15), - 3rd page is displayed (
d.`number` >= 10).
In this section, you can find query that finds number of hastags used with pagination.
xxxxxxxxxx1
SELECT2
d.`id`,3
d.`name`,4
d.`count`5
FROM (6
SELECT -- nested query7
@number := @number + 1 AS `number`, -- nested query (with assigned row number)8
h.`id`, -- nested query9
h.`name`, -- nested query10
COUNT(h.`name`) AS `count` -- nested query11
FROM (SELECT @number := 0) n, `hashtags` h -- nested query (with initialised `number` variable)12
GROUP BY h.`name` -- nested query13
) d14
WHERE d.`number` >= 10 AND d.`number` <= 15 -- WHERE clause used to find rows on proper page
In this section, you can find query that finds number of hastags used by posts ordered by popularity with pagination:
xxxxxxxxxx1
SELECT2
d.`id`,3
d.`name`,4
d.`count`5
FROM (6
SELECT -- nested query7
@number := @number + 1 AS `number`, -- nested query (with assigned row number)8
h.`id`, -- nested query9
h.`name`, -- nested query10
COUNT(h.`id`) AS `count` -- nested query11
FROM (SELECT @number := 0) n, `hashtags` h -- nested query (with initialised number variable)12
JOIN `posts` p ON p.`hashtag_id` = h.`id` -- nested query13
GROUP BY h.`id` -- nested query14
ORDER BY `count` DESC -- nested query (ordered from most popular hashtags)15
) d16
WHERE d.`number` >= 10 AND d.`number` <= 15 -- WHERE clause used to find rows on proper page
