Select top row for each ID - 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 2005 Forums

Transact-SQL (2005)

Select top row for each ID

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-07-24 : 13:35:20

Hi All,

Looks like very simple question, but I can't figure out how to do it.
I need to select top row for every caseid

declare table MyT (caseid int, track int, descr varchar(50))

insert into MyT
select 1, 28, 'aaa' union all
select 1, 16, 'sss' union all
select 1, 39, 'ddd' union all
select 3, 11, 'qqq' union all
select 3, 122, 'asd' union all
select 6, 872, 'qwe' union all
select 6, 22, 'ttt' union all
select 12, 34, 'rrr' union all
select 9, 12, 'ppp' union all
select 9, 14, 'kkk'

Expected result:
1, 28, 'aaa'
3, 11, 'qqq'
6, 872, 'qwe'
12, 34, 'rrr'
9, 12, 'ppp'

TIA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 13:41:47

How do you define "top row"?
There is no guarantee that resultset is consistent between two different selects.

E 12°55'05.76"
N 56°04'39.42"

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-07-24 : 13:44:25

What I meant by top row is the first occurence of caseid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 13:45:25

I KNOW!
What defines the order of records? Is there a primary key? Some datetime column?

E 12°55'05.76"
N 56°04'39.42"

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-07-24 : 13:49:37

Well, this is actually temp table and it doesn't have any primary key. The order is defined by the way the records were inserted in the table. I know it sounds nasty, what's your suggestion?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 13:49:41

As Peso mentioned there is no "order" between SELECTs as such. So first occurence could be 28 one time and 16 another. So its better if you defined the first occurence as a MIN/MAX of Track. At least if you have a PK (like an identity column) then you can order by that and decide the "first occurence". But based on the data provided, the first occurence could be any of the 3 or 4 records for each caseid.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-24 : 13:59:55

declare @MyT table(id int identity(1,1),caseid int, track int, descr varchar(50))

insert into @MyT
select 1, 28, 'aaa' union all
select 1, 16, 'sss' union all
select 1, 39, 'ddd' union all
select 3, 11, 'qqq' union all
select 3, 122, 'asd' union all
select 6, 872, 'qwe' union all
select 6, 22, 'ttt' union all
select 12, 34, 'rrr' union all
select 9, 12, 'ppp' union all
select 9, 14, 'kkk'

SELECT t1.*

FROM @myt t1
INNER JOIN
(select caseid,'MinId' = min(id)
from @myT
group by caseid) t2

ON
t1.caseid = t2.caseid
and t1.id = t2.MinId

Jim

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-07-24 : 14:07:40

Jim, thanks a lot.
That's exactly what I need.
Peso, Dinakar thank you for mentioning the necessity of PK for this task.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 14:34:27

If you are going to use SQL Server 2005, learn the new ways to deal with things.

SELECT CaseID, Track, Descr FROM (
SELECT CaseID, Track, Descr, ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY ID) AS RecID FROM @MyT
) AS d WHERE RecID = 1

E 12°55'05.76"
N 56°04'39.42"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources