September 12, 2008 at 8:10 am
Is there a way to pull the top 5 for each id?
ID Amount
1 4
1 3
1 34
1 2
1 4
1 234
1 45
1 54
1 43
7 6
7 5
7 3
7 4
7 342
7 2
7 434
7 243
7 242
So would like to pull
ID Amount
1 234
1 54
1 45
1 43
1 34
7 434
7 342
7 243
7 242
7 6
September 12, 2008 at 8:23 am
marty.seed (9/12/2008)
Is there a way to pull the top 5 for each id?ID Amount
1 4
1 3
1 34
1 2
1 4
1 234
1 45
1 54
1 43
7 6
7 5
7 3
7 4
7 342
7 2
7 434
7 243
7 242
So would like to pull
ID Amount
1 234
1 54
1 45
1 43
1 34
7 434
7 342
7 243
7 242
7 6
create table data (ID int, amount int)
--ID Amount
insert into data (ID, Amount) values ( 1, 4)
insert into data (ID, Amount) values ( 1, 3)
insert into data (ID, Amount) values ( 1, 34)
insert into data (ID, Amount) values ( 1, 2)
insert into data (ID, Amount) values ( 1, 4)
insert into data (ID, Amount) values ( 1, 234)
insert into data (ID, Amount) values ( 1, 45)
insert into data (ID, Amount) values ( 1, 54)
insert into data (ID, Amount) values ( 1, 43)
insert into data (ID, Amount) values ( 7, 6)
insert into data (ID, Amount) values ( 7, 5)
insert into data (ID, Amount) values ( 7, 3)
insert into data (ID, Amount) values ( 7, 4)
insert into data (ID, Amount) values ( 7, 342)
insert into data (ID, Amount) values ( 7, 2)
insert into data (ID, Amount) values ( 7, 434)
insert into data (ID, Amount) values ( 7, 243)
insert into data (ID, Amount) values ( 7, 242)
SELECT ID , Amount
FROM
( SELECT ID , Amount,
row_number () over (partition by ID order by Amount desc) rn
FROM data )t
WHERE rn <=5
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply