top 3 with conditions.... - 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

Transact-SQL (2000)

top 3 with conditions....

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-03-11 : 15:36:17

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', 37
Union All Select 'B', 'e', 28
Union All Select 'B', 'f', 06
Union All Select 'B', 'g', 14
Union All Select 'B', 'h', 90
Union All Select 'B', 'i', 2
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

This will get the top 5 rows...[url]http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx[/url]
What can I do to get the top 3 with rank in (37,14,2) and that the count must be 3 or greater.

Thanks...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 16:07:56

emmm... you mean like this?

Set @n = 3
Select Cat, subCat, rank
From @myTable as A
Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
and rank in (37,14,2)
Order By Cat, Rank Desc

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

hai
Yak Posting Veteran

84 Posts

Posted - 2008-03-11 : 16:23:26

Spirit...your query return:
Cat subCat rank
A c 14
A a 2
B g 14
B i 2

What I really want is this:
Cat subCat rank
A e 37
A c 14
A a 2
B d 37
B g 14
B i 2

I think I figure this thing out from your query....

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-11 : 16:33:31

select Cat, subCat, rank
from
(
select Cat, subCat, rank
from @myTable
where rank in (37,14,2)
) A
where (Select count(1) From @myTable Where rank in (37,14,2) and cat=A.cat and rank <= A.rank)<=@n
Order By Cat, Rank Desc

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources