Top 1 for Each Group Too Slow - 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 1 for Each Group Too Slow

Author

Topic

john_mitchell_1965
Starting Member

1 Post

Posted - 2004-09-17 : 01:24:31

I have a method that i use regularly in many views throughout my systems and am wondering whether there is a standard method for achieving the same results.

Basically I need to return from a table the top 1 in a particular order for each group in the table.

Suppose the following:
Table = Items
Columns = ID, GroupID, Order1, Order2
Records
=======
1,1,1,1
2,1,1,2
3,1,2,1
4,2,1,1
5,2,3,4
6,2,2,1
7,2,1,3
8,3,2,2
9,3,1,2

My view is as follows:

select *
from Items as i1
where ID = (
select top 1 ID
from Items
where GroupID = i1.GroupID
order by Order1, Order2)

Result:
1,1,1,1
4,2,1,1
9,3,1,2

Anyones help on this would be very appreciative.

Regards,
John

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-17 : 01:31:55

How big is the recordset? This shouldn't be too slow if it's indexed properly. You might want to try using WHERE EXISTS instead of the "=". Also, I would try a join on a derived table instead and see how that works.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 04:25:19

as you'r getting only the first use min function:


select i1.*
from @Items i1 
     inner join (select GroupID, min(order1) as order1, min(order2) as order2 from @Items group by GroupID) i2 on 
	i1.GroupID = i2.GroupID and i1.order1 = i2.order1 and i1.order2 = i2.order2

this should be faster on large recorsets.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources