How to select top .... by group? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to select top .... by group?

Author

Topic

p2bl
Yak Posting Veteran

54 Posts

Posted - 2002-01-12 : 21:53:00

Here is a table table1
id int identity(1,1)
title vachar(50)
groupid varchar(10)
......

The column groupid shows what group it belongs to,it looks like '1xxxxx','2xxxxxx'....
now,If I want to select top 10 records(order by id desc) every group,
how to do this?
I have use this
select * from table1 t1 where ((select count(*) from table1 t2 where t2.groupid=t1.groupid)<11)

It works.But,obviously,It is not a good solution.It takes many 'nested loop',and If the table contains many records,It will take much more time to execute it.So,where is the better one?Thanks a lot!

I do not know what is what

Nazim
A custom title

1408 Posts

Posted - 2002-01-12 : 23:47:04

If i have understood your requirements correctly

select * from tablename
where groupid in(
select groupid from tablename
group by groupid
having count(*)>10)
order by id desc

or
using top
read this Artcile by Graz , might be it will help you.

http://www.sqlteam.com/item.asp?ItemID=566

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-13 : 02:00:00

Hi

Nazim, I think you might have it wrong. The requirement is top 10 group 1s, then the top 10 group 2s etc

I think a correlated subquery is the best way to go there, so you are on the right track. Have you had a performance problem yet ?

Damian

Nazim
A custom title

1408 Posts

Posted - 2002-01-13 : 02:16:29

i did had doubt on that, Early Morning Hangover

if therez any performance problem create index on groupid , it should make it faster.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Lavos
Posting Yak Master

200 Posts

Posted - 2002-01-13 : 11:44:17

Creating an index might speed it up, but the best place for performance gains is usually in the algorithmns used. You're right, a correlated subquery and Top is probably the best method. I just returned the Guru's Guide back to the library, and I'm 99.9% sure that this is in there somewhere. (Heck, the last Reader's Challenge was in there)

I think this would work, but I haven't ran it to see if it works:

SELECT * FROM table1 t1
WHERE id in (SELECT TOP 10 id FROM table1 t2 WHERE t2.groupid = t1.groupid ORDER BY id DESC)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-13 : 17:13:33

quote:

I just returned the Guru's Guide back to the library, and I'm 99.9% sure that this is in there somewhere. (Heck, the last Reader's Challenge was in there)

Graz, Rob.... They are on to us

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources