top 3 records in 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

SQL Server 2005 Forums

Transact-SQL (2005)

top 3 records in group

Author

Topic

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-10-19 : 02:16:15

Hi

I have appno,qty,rate fields.
I am using following query :
Select * from tab order by appno,price desc,qty desc

From the result set of this query,I want the top 3 records
from the group of each appno.
How this can be done?

Plz help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 04:07:46

Please feel free to search SQLTeam forums.
There has been at least seven other requests last week regarding top 3 within group.

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

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 04:30:14

/sigh

Search for that specific phrase "top 3 within group" provides over 70 hits, nearly all of them relevant

I'm getting fed up with answering "I can't be bothered" posters ... you too by the "reads" of it!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-19 : 04:30:20

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

tm
Posting Yak Master

160 Posts

Posted - 2007-10-19 : 11:00:01

quote:
Originally posted by madhivanan

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Can anyone confirm this or am I doing something wrong ..
I went to the link above and looked at
"2. How to get top N rows for each group?"

The example given is this:

Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )
Insert Into @myTable
Select 'A', 'a', 2
Union All Select 'A', 'b', 52
Union All Select 'A', 'c', 14
Union All Select 'A', 'd', 46
Union All Select 'A', 'e', 37
Union All Select 'A', 'f', 95
Union All Select 'A', 'g', 73
Union All Select 'A', 'h', 67
Union All Select 'A', 'i', 80
Union All Select 'A', 'j', 03
Union All Select 'B', 'a', 18
Union All Select 'B', 'b', 44
Union All Select 'B', 'c', 52
Union All Select 'B', 'd', 60
Union All Select 'B', 'e', 28
Union All Select 'B', 'f', 06
Union All Select 'B', 'g', 70
Union All Select 'B', 'h', 90
Union All Select 'B', 'i', 89
Union All Select 'B', 'j', 31
declare @n int
Set @n = 5
Select Cat, subCat, rank
From @myTable as A
Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
Order By Cat, Rank Desc

The actual result I got is this :

Cat subCat rank
---------- ---------- -----------
A d 46
A e 37
A c 14
A j 3
A a 2
B b 44
B j 31
B e 28
B a 18
B f 6

But I was expecting :

Cat subCat rank
---------- ---------- -----------
A f 95
A i 80
A g 73
A h 67
A b 52
B h 90
B i 89
B g 70
B d 60
B c 52

Am I misunderstanding what the script is doing?

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 11:06:54

That would be:

Where (Select count(1) From @myTable Where cat=A.cat and rank >= A.rank)<=@n

Note that there are more appropriate ways of doing this if you are using SQL 2005

Kristen

tm
Posting Yak Master

160 Posts

Posted - 2007-10-19 : 11:20:19

quote:
Originally posted by Kristen

That would be:

Where (Select count(1) From @myTable Where cat=A.cat and rank >= A.rank)<=@n

Note that there are more appropriate ways of doing this if you are using SQL 2005

Kristen

Thanks Kristen.

This is a typo on actual site but I should have looked closer at the script.

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 11:42:39

Haven't read the article, but I suppose how you read it. "top 5" could be considered to be "rows above when sorted in order" rather than "rows with higher values".

Anyway, I'm guessing! but if you are using SQL 2005 I recommend using CTE / ROW_NUMBER() methods instead

Kristen

tm
Posting Yak Master

160 Posts

Posted - 2007-10-19 : 12:09:41

quote:
Originally posted by Kristen

Haven't read the article, but I suppose how you read it. "top 5" could be considered to be "rows above when sorted in order" rather than "rows with higher values".

Anyway, I'm guessing! but if you are using SQL 2005 I recommend using CTE / ROW_NUMBER() methods instead

Kristen

I looked into what you mentioned regarding CTE / ROW_NUMBER() but cannot grasp what the recommended way would be. Would you be able to point me in the right direction?

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 12:22:58

No problem ... I think these links are probably representative:

Article about the whole 2005 paging deal:

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

I think this was a useful discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90388#338376

and this, although there are a number of different possible end-user scenario being tested in parallel - which complicates the code a bit - but Peso has retrieved the total number of rows (to get a Page Number) as well as just the rows for "Page N"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91068#342136

Kristen

tm
Posting Yak Master

160 Posts

Posted - 2007-10-19 : 13:44:35

quote:
Originally posted by Kristen

No problem ... I think these links are probably representative:

Article about the whole 2005 paging deal:

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

I think this was a useful discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90388#338376

and this, although there are a number of different possible end-user scenario being tested in parallel - which complicates the code a bit - but Peso has retrieved the total number of rows (to get a Page Number) as well as just the rows for "Page N"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91068#342136

Kristen

Thank you so much Kristen. I got this to work and it's great.

Declare @myTable table (cat varchar(10), subcat varchar(10), [rank] int )
Insert Into @myTable
Select 'A', 'a', 2
Union All Select 'A', 'b', 52
Union All Select 'A', 'c', 14
Union All Select 'A', 'd', 46
Union All Select 'A', 'e', 37
Union All Select 'A', 'f', 95
Union All Select 'A', 'g', 73
Union All Select 'A', 'h', 67
Union All Select 'A', 'i', 80
Union All Select 'A', 'j', 03
Union All Select 'B', 'a', 18
Union All Select 'B', 'b', 44
Union All Select 'B', 'c', 52
Union All Select 'B', 'd', 60
Union All Select 'B', 'e', 28
Union All Select 'B', 'f', 06
Union All Select 'B', 'g', 70
Union All Select 'B', 'h', 90
Union All Select 'B', 'i', 89
Union All Select 'B', 'j', 31

declare @n int;

Select @n = 5

;WITH CTE (cat, subcat, [rank], rownum) AS
(
Select Cat, subCat, rank, row_number() OVER (partition by Cat order by cat, [rank] desc)
from @myTable
)
Select Cat, subCat, [rank], rownum
From CTE A
where rownum between 1 and @n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources