Select first record is a set - 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)

Select first record is a set

Author

Topic

mparkhouse
Starting Member

5 Posts

Posted - 2009-10-07 : 09:00:59

I have the following in a table:

ID Category
12345 Category 1
12345 Category 2
12345 Category 3
23456 Category 2
23456 Category 4
34567 Category 5
34567 Category 1
34567 Category 4

I need a select statement that will pull the first ID/Category pair for each ID.

I'm pretty sure there is a way to put a count on each of the records shown above into a temp table and then choose the records from the temp table with a count of 1. However, I can't remember how to generate the count field.

Any help would be greatly appreciated.

Thanks,

Mary

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 09:59:34

Is this?

select id,min(category) as category from table
group by id

Madhivanan

Failing to plan is Planning to fail

mparkhouse
Starting Member

5 Posts

Posted - 2009-10-07 : 10:02:21

No, because I want the first one that was entered in the list and I'm not sure what the min() function would return with a varchar field.

Thanks for answering.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 10:09:39

quote:
Originally posted by mparkhouse

No, because I want the first one that was entered in the list and I'm not sure what the min() function would return with a varchar field.

Thanks for answering.

Do you have any other unique column?

Madhivanan

Failing to plan is Planning to fail

mparkhouse
Starting Member

5 Posts

Posted - 2009-10-07 : 10:18:55

Yes, there is a sequence number for each record which is unique.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-07 : 10:25:21

quote:
Originally posted by mparkhouse

Yes, there is a sequence number for each record which is unique.

sequence number is a auto generated id?? show samples!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-07 : 10:35:38

select t1.* from table as t1 inner join
(
select id,min(sequence_number) as sequence_number from table
group by id
) as t2
on t1.id=t2.id and t1.sequence_number=t2.sequence_number

Madhivanan

Failing to plan is Planning to fail

mparkhouse
Starting Member

5 Posts

Posted - 2009-10-07 : 10:54:58

Thanks! The last one did it. Using min() on the sequence number was the key.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources