top 4 per group - 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

General SQL Server Forums

New to SQL Server Programming

top 4 per group

Author

Topic

EZEK
Starting Member

8 Posts

Posted - 2008-01-28 : 10:36:00

hi guys, this is my first post here.

i am trying to do a query that returns the top 4 best tax of each bank, for example in a table i have bank and tax. if i select top 4 * it will return only 4 records and what i want is the following:
bank 1 0,1
bank 1 0,11
bank 1 0,12
bank 1 0,13
bank 2 0,09
bank 2 0,1
bank 2 0,11
bank 2 0,12
...

i was trying to group by but it didnt work
if anyone can help me...

thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 10:41:55

SELECT BankName, Interest FROM (
SELECT BankName, Interest, ROW_NUMBER() OVER (PARTITION BY BankName ORDER BY Interest DESC) AS RecID FROM Table1
) AS d WHERE RecID < 5

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 10:45:32

If its SQL 2005:-

SELECT tmp.Bank,
       tmp.Tax
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY t.Bank ORDER BY t.Tax DESC) AS RowNo,
t.Bank,
t.Tax
FROM Table)tmp
WHERE tmp.RowNo<=4

if its SQL 2000:-

SELECT tmp.Bank,
       tmp.Tax
FROM
(
SELECT t.Bank,
       t.Tax,
       (SELECT COUNT(*) +1 FROM Table WHERE Bank=t.Bank AND Tax>t.Tax) AS RowNo
FROM Table
)tmp
WHERE tmp.RowNo<=4

EZEK
Starting Member

8 Posts

Posted - 2008-01-28 : 10:49:01

thx peso i love you man

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources