Geek asks and answers

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 a SQL query that will return three top scores for each player in descending order “grouped” by player ie

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

Very grateful for any pointers.

+3
source share
4 answers

This is an old-fashioned (read: basic sql) way to create top-n for each group. You can join the table for yourself in a group state (here it is a player) and select records with a higher score on the right side; if there are three or fewer such entries, the row is one of the top n rows for each 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

An alternative version that you can check using does not exist:

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

- ( ), , , , .

Sql Fiddle.

+3

, , , :

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

, RANK. :

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

" " .

, MySQL, ( Microsoft SQL). RANK RANK. . :

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

, , .

+2

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
+2
source

Depending on the DBMS you are using, you can 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
+1
source

More articles:


All Articles