Distinct fields - 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

General SQL Server Forums

New to SQL Server Programming

Distinct fields

Author

Topic

k1t3
Starting Member

5 Posts

Posted - 2009-12-07 : 23:51:49

Hi, is there a way to query from two tables where in the result in one particular field are unique?


SELECT DISTINCT E.ID E.First, E.Last, Pos.PosID, Pos.Desc
FROM E INNER JOIN Pos ON E.PosID = Pos.PosID

The query above gives this result:
1 | John | Doe | 1 | Trainee
1 | John | Doe | 2 | Programmer

I need John Doe to appear in the list only once despite him having 2 positions. Is there a way to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-08 : 00:28:09

so which position do you want to see ?

KH
[spoiler]Time is always against us[/spoiler]

k1t3
Starting Member

5 Posts

Posted - 2009-12-08 : 00:35:00

I'd like to see the first position (trainee), if possible.

Thanks, btw.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-08 : 00:44:53

are you using SQL 2000 or 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-08 : 01:05:02

try this

SELECT T.ID,T.First,T.Last,T.PosID,T.Desc
FROM (
SELECT DISTINCT
E.ID, E.First, E.Last, Pos.PosID, Pos.Desc,ROW_NUMBER() OVER(PARTITION BY E.ID, E.First, E.Last ORDER BY Pos.PosID ) as RowNum
FROM E
INNER JOIN Pos ON E.PosID = Pos.PosID
) WHERE T.RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources