get id from distinct column - 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)

get id from distinct column

Author

Topic

Stalknecht
Starting Member

22 Posts

Posted - 2006-07-03 : 08:53:11

imagine th folowing table:
id color
1 blue
2 white
3 blue
4 blue
5 black

my desired output is:
1
2
5

I wanna have the first id from an distinct select of the color

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 08:57:24

Your question puzzles me. If first means lowest ID for each color, then your desired output is created with

SELECT MIN(ID) FROM MyTable GROUP BY Color

but every distinct color is

SELECT MIN(ID) FROM MyTable GROUP BY Color HAVING COUNT(*) = 1

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 09:00:53

quote:
Originally posted by Peso

Your question puzzles me.

SELECT ID FROM MyTable GROUP BY Color HAVING COUNT(*) = 1

Peter Larsson
Helsingborg, Sweden

I think that should be

SELECT min(ID) FROM @t GROUP BY Color

Madhivanan

Failing to plan is Planning to fail

Stalknecht
Starting Member

22 Posts

Posted - 2006-07-03 : 16:49:51

Thanks for the solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources