CODEWITHSUNDEEP
mysqlsql
user1350000
user1350000

Reputation: 41

SQL query to return the three highest values for one column "grouped" by another column

Let's say I have a table like this:

Player  Score
A       5
B       4
A       3
B       2
A       1
B       1
A       2
B       3
A       4
B       5

I need an SQL query that will return the three highest scores per player in descending order "grouped" by player i.e.

Player  Score
A       5
A       4
A       3
B       5
B       4
B       3

Very grateful for any pointers.

Upvotes: 3

Views: 6457

Answers (4)

Jonny
Jonny

Reputation: 2917

in SQL server:

select p.player, p.score
from PS p
where p.score in (select top 3 score from PS 
                 where player = p.player order by score desc)
order by p.player asc, p.score desc

in MySql:

select p.player, p.score
    from PS p
    where p.score in (select score from PS 
                     where player = p.player order by score desc limit 3)
    order by p.player asc, p.score desc

Upvotes: 3

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

This is old-fashioned (read: basic sql) way of producing top-n per group. You might join the table to itself on group condition (here it is player) and pick records with higher score on right side; if there are three or less such records, the row is one of top n rows per group.

select player.player, player.score
from Player
left join Player p2
on p2.player = player.player
  and p2.score > player.score
group by player.player, player.score
having count(distinct p2.score) < 3
order by 1, 2 desc

Alternative version you might check, using not exists:

select player, score
from player
where not exists
(
  select p2.player
  from Player p2
  where p2.player = player.player
  and p2.score > player.score
  group by p2.player
  having count(distinct p2.score) > 3
)
order by 1, 2 desc

This two versions differ in presentation of ties - while first one returns one row (by nature of group by) and needs to be joined back to original table to show all records, second one works directly from original table showing all data and ties at once.

You can find Demo at Sql Fiddle.

Upvotes: 3

IAmTimCorey
IAmTimCorey

Reputation: 16757

I think what you are looking for can be found here:

http://www.sql-ex.ru/help/select16.php

Basically, the best solution uses the RANK function. Here is the example code from the site:

SELECT maker, model, type FROM
(
SELECT maker, model, type, RANK() OVER(PARTITION BY type ORDER BY model) num
FROM Product
) X
WHERE num <= 3

You would just need to modify the Partition By section to order by your score in descending order.

EDIT

Based upon the information that you will be using MySQL, you will need to make some modifications to the above query (which works with Microsoft SQL). You need to replace the RANK function with your own RANK implementation. It isn't that hard. Complete instructions can be found here:

http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

That will show you how to implement a counter that can give you a rank number.

Upvotes: 2

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

Depending on what DBMS you use, you may be able to use row_number in some form

In SQL Server 2008 you can use

create table #player
( Player char, Score int )

insert into #player (Player, Score) Values
('A',5),('B',4),('A',3),('B',2),('A',1),('B',1),('A',2),('B',3),('A',4),('B',5)

select * from #player

select Player, Score from 
(
  select *, ROW_NUMBER() over(partition by Player order by Score desc) as rowNo 
  from #player
) as tmp
where tmp.rowNo <= 3

drop table #player

Upvotes: 1

Related Questions