group by + rowcount - 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 2000 Forums

SQL Server Development (2000)

group by + rowcount

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2005-02-10 : 05:10:15

I have a simple query with group by.
and I want to get the topX rows from each group.
how to do that ?

Noam Graizer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-10 : 05:46:02

will this work for you?


declare @n int 
Set @n = 5
Select id, comment, someDate
From   myTable as t1
Where  (Select count(distinct someDate) from myTable Where id = t1.id and someDate >= t1.someDate)<=@n
Order By id, someDate desc

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-10 : 06:30:38

Here is other solution

Declare @n int
Set @n=3

Select no,n from(
select T1.*,SNo=count(*) from TableName T1 inner join TableName T2 on T1.no=T2.no and T1.n>=T2.n
group by T1.no, T1.n ) T where Sno<=@n order by no

Madhivanan

noamg
Posting Yak Master

215 Posts

Posted - 2005-02-10 : 07:35:38

what n means ?

Noam Graizer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-10 : 07:45:02

n means the number of row in the group you want to return.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-10 : 08:07:53

Sorry it should be Sno. You can omit this from selection. Thanks spirit1

Madhivanan

noamg
Posting Yak Master

215 Posts

Posted - 2005-02-10 : 08:59:27

sorry it doesn't work.
for example:
declare @myTable table ( id int identity(1,1), -- PK
comment int,
someDate datetime
)

Noam Graizer

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-10 : 09:06:49

quote:
Originally posted by noamg

sorry it doesn't work.
for example:
declare @myTable table ( id int identity(1,1), -- PK
comment int,
someDate datetime
)

Noam Graizer

"It doesn't work". Very useful feedback!

what SQL statement did you try? did it give you an error? wrong results?

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-10 : 09:09:02

what do you mean it doesn't work?? of course it does.
do you have any idea how this works?
you shouldn't just copy paste it. understand it first.


Declare @myTable table (category varchar(100), subcategory 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 'C', 'g', 70 
Union All Select 'C', 'h', 90 
Union All Select 'C', 'i', 89 
Union All Select 'C', 'j', 31

declare @n int
Set @n = 2
Select category, subcategory, rank
From @myTable as t1
Where (Select count(distinct rank) From @myTable Where category=t1.category and rank <= t1.rank)<=@n
Order By category, Rank Desc

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-10 : 09:30:49

With all the specific questions asked like this, perhaps there oughta be an index on this site that would highlight problems like this.

Just a suggestion.

Semper fi, Xerxes, USMC(Ret.)
----------------------------------------------
Acclaimed Cursoring Yak Master
"If cursors were nickels, I could park in town--all year!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources