Top 3 on a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Top 3 on a table

Author

Topic

FernandoLorival
Starting Member

19 Posts

Posted - 2011-03-30 : 15:07:49

Hi all,
I came across with a problem that I can't find a solution for, hope someone here can help.

I need to display the top 3 values for all the players in a table that has more then 3 records per player.
Here's an example:

--creates the example table
CREATE TABLE #temp
( ID int,
Name char(30),
Value int )
--Inserts values
insert into #temp(id,name,value) values (1,'F1',14)
insert into #temp(id,name,value) values (2,'F1',18)
insert into #temp(id,name,value) values (3,'F1',12)
insert into #temp(id,name,value) values (4,'F1',5)
insert into #temp(id,name,value) values (5,'F1',7)
insert into #temp(id,name,value) values (6,'F1',15)
insert into #temp(id,name,value) values (7,'F2',12)
insert into #temp(id,name,value) values (8,'F2',4)
insert into #temp(id,name,value) values (9,'F2',8)
insert into #temp(id,name,value) values (10,'F2',12)
insert into #temp(id,name,value) values (11,'F3',15)
insert into #temp(id,name,value) values (12,'F3',7)
insert into #temp(id,name,value) values (13,'F3',5)
insert into #temp(id,name,value) values (14,'F3',12)
insert into #temp(id,name,value) values (15,'F3',11)
insert into #temp(id,name,value) values (16,'F3',8)
-- shows values
select * from #temp

As you can see from all the lines I just need the top 3...

The closest thing I got was with a UNION statement, something like:

select top 3 * from #temp WHERE NAME = 'F1'
UNION
select top 3 * from #temp WHERE NAME = 'F2'
UNION
select top 3 * from #temp WHERE NAME = 'F3'

but that will not work because I dont know all the NAMES for the WHERE CLAUSE and also the values are not in the correct order (SORT)

My end result needs to be something like this:

ID Name Value
----------- ------------------------------ -----------
2 F1 18
6 F1 15
1 F1 14
7 F2 12
10 F2 12
9 F2 8
11 F3 15
14 F3 12
15 F3 11

Thanks in advance for all your help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-30 : 15:20:26

select *
from
(
select name,value,rown = rank() over(partition by name order by value desc) from #temp
) a

where rown <4

Jim

Everyday I learn something that somebody else already knew

FernandoLorival
Starting Member

19 Posts

Posted - 2011-03-30 : 15:28:08

Excellent, that works.
Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources