JOIN on table itself

[Naoise]

I have a table int, int, varchar, varchar

IDA IDB foo bar
1053 106 ss a@a.com
1054 107 dws a@a.com
1061 114 a b@b.com
1062 114 a b@b.com
1063 114 a b@b.com
1064 115 t b@b.com

How would I get a resultset with single records for each IDB based on its lowest IDA, ie the following results

IDA IDB foo bar
1053 106 ss a@a.com
1054 107 dws a@a.com
1061 114 a b@b.com
1064 115 t b@b.com

 

[PHV]

SELECT A.IDA, A.IDB, A.foo, A.bar
FROM yourTable A INNER JOIN (
SELECT MIN(IDA) minIDA, IDB FROM yourTable GROUP BY IDB
) B ON A.IDA = B.minIDA AND A.IDB = B.IDB

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Naoise]

Nice, I never knew you could INNER JOIN a SELECT statement and apply a reference like that to it. Thanks