MySQL - SELECT query with GROUP BY and LIMIT

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:

1. use nested query to number rows, e.g. article is here,
2. then wrap the query using WHERE clause to find rows on proper page.

 Quick solution:

SELECT d.*
FROM (
	SELECT                                       -- nested query
		@number := @number + 1 AS `number`,      -- nested query (with assigned row number)
		h.*,                                     -- nested query
		COUNT(h.`name`) AS `count`               -- nested query
	FROM (SELECT @number := 0) n, `hashtags` h   -- nested query (with initialised `number` variable)
	GROUP BY h.`name`                            -- nested query
) d
WHERE d.`number` >= 10 AND d.`number` <= 15      -- WHERE clause used to find rows on proper page

Where it is assumed:

• page has 5 rows (d.`number` >= 10 AND d.`number` <= 15),
• 3rd page is displayed (d.`number` >= 10).

Simple example

In this section, you can find query that finds number of hastags used with pagination.

SELECT
	d.`id`,
	d.`name`,
	d.`count`
FROM (
	SELECT                                       -- nested query
		@number := @number + 1 AS `number`,      -- nested query (with assigned row number)
		h.`id`,                                  -- nested query
		h.`name`,                                -- nested query
		COUNT(h.`name`) AS `count`               -- nested query
	FROM (SELECT @number := 0) n, `hashtags` h   -- nested query (with initialised `number` variable)
	GROUP BY h.`name`                            -- nested query
) d
WHERE d.`number` >= 10 AND d.`number` <= 15      -- WHERE clause used to find rows on proper page

Complex example

In this section, you can find query that finds number of hastags used by posts ordered by popularity with pagination:

SELECT
	d.`id`,
	d.`name`,
	d.`count`
FROM (
	SELECT                                       -- nested query
		@number := @number + 1 AS `number`,      -- nested query (with assigned row number)
		h.`id`,                                  -- nested query
		h.`name`,                                -- nested query
		COUNT(h.`id`) AS `count`                 -- nested query
	FROM (SELECT @number := 0) n, `hashtags` h   -- nested query (with initialised number variable)
	JOIN `posts` p ON p.`hashtag_id` = h.`id`    -- nested query
	GROUP BY h.`id`                              -- nested query
	ORDER BY `count` DESC                        -- nested query (ordered from most popular hashtags)
) d
WHERE d.`number` >= 10 AND d.`number` <= 15      -- WHERE clause used to find rows on proper page

See also

1. MySQL - get row position with SELECT query