Alex Rivera | Logout

Restrict results to top N rows per group

Asked 2010-01-25T01:25:02.777
548

The following query:

SELECT
    year, id, rate
FROM h
WHERE year BETWEEN 2000 AND 2009
ORDER BY id, rate DESC

yields:

year | id  | rate
2006 | p01 |  8.0
2003 | p01 |  7.4
2008 | p01 |  6.8
2001 | p01 |  5.9
2007 | p01 |  5.3
2009 | p01 |  4.4
2002 | p01 |  3.9
2004 | p01 |  3.5
2005 | p01 |  2.1
2000 | p01 |  0.8
2001 | p02 | 12.5
2004 | p02 | 12.4
2002 | p02 | 12.2
2003 | p02 | 10.3
2000 | p02 |  8.7
2006 | p02 |  4.6
2007 | p02 |  3.3

I want only the top 5 rows for each id:

year | id  | rate
2006 | p01 |  8.0
2003 | p01 |  7.4
2008 | p01 |  6.8
2001 | p01 |  5.9
2007 | p01 |  5.3
2001 | p02 | 12.5
2004 | p02 | 12.4
2002 | p02 | 12.2
2003 | p02 | 10.3
2000 | p02 |  8.7

Is there a way to do this using some kind of LIMIT like modifier that applies to each group?

sql mysql greatest-n-per-group ranking
Edit
Report

1 Answer

14

No, you can't LIMIT subqueries arbitrarily (you can do it to a limited extent in newer MySQLs, but not for 5 results per group).

This is a groupwise-maximum type query, which is not trivial to do in SQL. There are various ways to tackle that which can be more efficient for some cases, but for top-n in general you'll want to look at Bill's answer to a similar previous question.

As with most solutions to this problem, it can return more than five rows if there are multiple rows with the same rate value, so you may still need a quantity of post-processing to check for that.

answered 2010-01-25T01:37:08.147

Your Answer