getting top 3 result of each group

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)

getting top 3 result of each group

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-08 : 15:53:25

i have a table:
id,categoryref,datatype,innerOrder
1,3,1,11
2,3,1,19
3,3,1,2
4,2,1,8
5,2,1,6
6,3,1,3
7,3,1,1
........
and i want to get first 3 resukts where datatype=1
and categoryref=3 and order by innerORder (where innerORder is the order for each categoryref)
how do i do this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-08 : 16:17:47

how long have you been here pelegk2?

anyways...

select top 3 *
from results
where datatype=1 and categoryref=3
order by innerorder asc

--------------------
keeping it simple...

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-09-08 : 16:23:32

sorry mu mistake wihtought the categoryref=3
i need all the groups:)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-08 : 20:13:54

[code]
create table #Test(id int ,categoryref int,datatype int,innerOrder int)
insert into #Test(ID,categoryref,datatype,innerorder)
select 1,3,1,11
union all
select 2,3,1,19
union all
select 3,3,1,2
union all
select 4,2,1,8
union all
select 5,2,1,6
union all
select 6,3,1,3
union all
select 7,3,1,1

Select *
from
(
select
row_Number() over (partition by categoryref order by innerorder) as rowID
,*
from #Test
) a
where a.RowID <=3
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources