The following is the simplest possible example, though any solution should be able to scale to however many n top results are needed:
Given a table like that below, with person, group, and age columns, how would you get the 2 oldest people in each group? (Ties within groups should not yield more results, but give the first 2 in alphabetical order)
+--------+-------+-----+ | Person | Group | Age | +--------+-------+-----+ | Bob | 1 | 32 | | Jill | 1 | 34 | | Shawn | 1 | 42 | | Jake | 2 | 29 | | Paul | 2 | 36 | | Laura | 2 | 39 | +--------+-------+-----+
Desired result set:
+--------+-------+-----+ | Shawn | 1 | 42 | | Jill | 1 | 34 | | Laura | 2 | 39 | | Paul | 2 | 36 | +--------+-------+-----+
NOTE: This question builds on a previous one- Get records with max value for each group of grouped SQL results - for getting a single top row from each group, and which received a great MySQL-specific answer from @Bohemian:
select *
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`
Would love to be able to build off this, though I don't see how.
How about using self-joining:
gives me:
I was strongly inspired by the answer from Bill Karwin to Select top 10 records for each category
Also, I'm using SQLite, but this should work on MySQL.
Another thing: in the above, I replaced the
group
column with agroupname
column for convenience.Edit:
Following-up on the OP's comment regarding missing tie results, I incremented on snuffin's answer to show all the ties. This means that if the last ones are ties, more than 2 rows can be returned, as shown below:
gives me:
In other databases you can do this using
ROW_NUMBER
. MySQL doesn't supportROW_NUMBER
but you can use variables to emulate it:See it working online: sqlfiddle
Edit I just noticed that bluefeet posted a very similar answer: +1 to him. However this answer has two small advantages:
So I'll leave it here in case it can help someone.
If the other answers are not fast enough Give this code a try:
Output:
Try this:
DEMO
There is a really nice answer to this problem at MySQL - How To Get Top N Rows per Each Group
Based on the solution in the referenced link, your query would be like:
where
n
is thetop n
andyour_table
is the name of your table.I think the explanation in the reference is really clear. For quick reference I will copy and paste it here:
I wanted to share this because I spent a long time searching for an easy way to implement this in a java program I'm working on. This doesn't quite give the output you're looking for but its close. The function in mysql called
GROUP_CONCAT()
worked really well for specifying how many results to return in each group. UsingLIMIT
or any of the other fancy ways of trying to do this withCOUNT
didn't work for me. So if you're willing to accept a modified output, its a great solution. Lets say I have a table called 'student' with student ids, their gender, and gpa. Lets say I want to top 5 gpas for each gender. Then I can write the query like thisNote that the parameter '5' tells it how many entries to concatenate into each row
And the output would look something like
You can also change the
ORDER BY
variable and order them a different way. So if I had the student's age I could replace the 'gpa desc' with 'age desc' and it will work! You can also add variables to the group by statement to get more columns in the output. So this is just a way I found that is pretty flexible and works good if you are ok with just listing results.