0

I have a table with a simple schema: user_id, score and I would like to extract the n records with the highest score, such that each user_id is only represented once.

This question is similar to Get top n records for each group of grouped results except that instead of needing n representatives per group, I need n representatives of the table, with a maximum of one record per group.

As such, if i'm looking for n=3, running the query on:

user_id | score
      1 |    10
      2 |     9
      1 |    11
      2 |     8
      3 |     8
      4 |    12

should yield:

user_id | score
      4 |     12
      1 |     11
      2 |      9
Community
  • 1
  • 1
Mala
  • 14,178
  • 25
  • 88
  • 119

1 Answers1

2
SELECT user_id, MAX(score) FROM table GROUP BY user_id ORDER BY MAX(score) DESC LIMIT 3;
AMADANON Inc.
  • 5,753
  • 21
  • 31
  • oh wow, i was gymnasting through self joins and subqueries and all that stuff, so glad it has an easy solution! – Mala Jun 28 '13 at 04:01
  • 1
    @Mala: Then you are still thinking `RBAR - Row By Agonizing Row`. Instead learn to think `column by column`; in this case, max score for top 3 users leads quickly to this solution. – Pieter Geerkens Jun 28 '13 at 04:08
  • If I added a date column to the table, how could I extend this to return the correct date? The trivial extension of selecting the date as well returns the first date instead of the date connected with the max score – Mala Jun 28 '13 at 15:27