paring problem - 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)

paring problem

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-08-30 : 06:47:31

[code]
need help getting the result

ID ID1
1 2
2 4
2 3
3 4
3 1
6 5

Expected Result ...
ID ID1
1 2
3 4
6 5

all records that has no previous
matches will be selected...
[/code]

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 07:08:53

[code]SELECT MAX(ID) AS ID,
ID1
FROM (
SELECT s1.ID,
s1.ID1
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.ID = s1.ID1
WHERE s2.ID IS NULL

UNION

SELECT s1.ID,
s1.ID1
FROM @Sample AS s1
WHERE s1.ID NOT IN (SELECT s2.ID1 FROM @Sample AS s2 WHERE s2.ID < s1.ID)
) AS d
GROUP BY ID1[/code]

E 12°55'05.25"
N 56°04'39.16"

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-08-30 : 07:25:29

thanks

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources