Limiting Per Each Group in MySQL
Not sure what this type of query is actually called. The basic idea is you have multiple tables linked together, but you want to only grab n rows from Table C per iteration of each row in Table A.
A more illustrative explanation: Suppose you have a table listing 10 albums and a table listing 100 records. You only want 5 records per each album.
As you might expect, Googling such a solution was pretty hard. A common solution was:
However, this returns a flat 5 records total, not per album.
Took me a bunch of playing around to finally come up with something like this:
So far this works but I'm not sure whether it's the most efficient way to go about it. Any improvements are definitely welcome. Hopefully this helps those of you also looking for the same solution but having trouble find it.
A more illustrative explanation: Suppose you have a table listing 10 albums and a table listing 100 records. You only want 5 records per each album.
As you might expect, Googling such a solution was pretty hard. A common solution was:
Code:
SELECT r.* FROM albums as a
LEFT JOIN (
SELECT * FROM records LIMIT 5) r ON r.album_id=a.album_id
However, this returns a flat 5 records total, not per album.
Took me a bunch of playing around to finally come up with something like this:
Code:
SELECT r.* FROM albums as a
LEFT JOIN records as r ON r.album_id=a.album_id
WHERE 5>(
SELECT count(rr.id) FROM records as rr
WHERE rr.album_id=a.album_id
AND rr.id>r.id)
So far this works but I'm not sure whether it's the most efficient way to go about it. Any improvements are definitely welcome. Hopefully this helps those of you also looking for the same solution but having trouble find it.
1122 unique view(s)
