Is there a way to get LIMIT results for each group of result rows in MySQL?
I have the following request:
SELECT title, karma, DATE(date_uploaded) as d
FROM image
ORDER BY d DESC, karma DESC
This will give me a list of image posts, first sorted by last day and then by most karma.
There is only one thing: I only want to receive the x highest karma images per day. So, for example, per day I only want the 10 most images of karma. I could of course run multiple queries, one per day, and then combine the results.
I was wondering if there is a smarter way that still works well. I'm guessing I'm looking for a way to use LIMIT x, y for each group of results?
a source to share
You can do this by emulating ROW_NUMBER with variables.
SELECT d, title, karma
FROM (
SELECT
title,
karma,
DATE(date_uploaded) AS d,
@rn := CASE WHEN @prev = UNIX_TIMESTAMP(DATE(date_uploaded))
THEN @rn + 1
ELSE 1
END AS rn,
@prev := UNIX_TIMESTAMP(DATE(date_uploaded))
FROM image, (SELECT @prev := 0, @rn := 0) AS vars
ORDER BY date_uploaded, karma DESC
) T1
WHERE rn <= 3
ORDER BY d, karma DESC
Result:
'2010-04-26', 'Title9', 9
'2010-04-27', 'Title5', 8
'2010-04-27', 'Title6', 7
'2010-04-27', 'Title7', 6
'2010-04-28', 'Title4', 4
'2010-04-28', 'Title3', 3
'2010-04-28', 'Title2', 2
Quassnoi has a good article on this that explains the technique in more detail: Emulating ROW_NUMBER () in MySQL - Fetching Rows .
Test data:
CREATE TABLE image (title NVARCHAR(100) NOT NULL, karma INT NOT NULL, date_uploaded DATE NOT NULL);
INSERT INTO image (title, karma, date_uploaded) VALUES
('Title1', 1, '2010-04-28'),
('Title2', 2, '2010-04-28'),
('Title3', 3, '2010-04-28'),
('Title4', 4, '2010-04-28'),
('Title5', 8, '2010-04-27'),
('Title6', 7, '2010-04-27'),
('Title7', 6, '2010-04-27'),
('Title8', 5, '2010-04-27'),
('Title9', 9, '2010-04-26');
a source to share
Maybe this will work:
SELECT title, karma, DATE (date_uploaded) as d FROM img image WHERE id IN (SELECT id FROM image WHERE DATE (date_uploaded) = DATE (img.date_uploaded) ORDER KARMA DESC LIMIT 10) ORDER BY d DESC, karma DESC
But this is not very efficient since you don't have an index on DATE (date_uploaded) (I don't know if this is possible, but I guess it is not). This can become very costly as the table grows. It might be easier to just have a loop in your code :-).
a source to share