only rows with min(date) help please

[r0brh0des]

I need to retrieve ONE row for every source id that has the lowest date coinciding with each id number.
source table:

id# sourceID Date
1 a1 2-3-02
2 c4 3-7-01
2 d3 4-12-98
3 5f 6-3-95
4 d5 5-22-02
4 2w 3-11-97
4 3v 1-15-01
5 3a 2-17-03


desired result:

id# sourceID Date
1 a1 2-3-02
2 d3 4-12-98
3 5f 6-3-95
4 2w 3-11-97
5 3a 2-17-03

I tried min() on date column omitting sourceID I get the right rows but I need the sourceID too and if I add in I get all of them not just the ones with the min(date) with distinct also.

close as I can get but no good:

select distinct id#, sourceID, min(date)
from source table,
group by id#, sourceID
order by id#

 

[PHV]

something like this ?
SELECT A.id#, A.sourceID, A.Date
FROM yourTable INNER JOIN (
SELECT id#, Min(Date) AS MinDate FROM yourTable GROUP BY id#
) AS M ON A.id# = M.id# AND A.Date = M.MinDate

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

 

[r0brh0des]

OH yeah!......that did the trick.

Major thanks PH.

although needed to add A alias for table in syntax:

SELECT A.id#, A.sourceID, A.Date
FROM yourTable A INNER JOIN (
SELECT id#, Min(Date) AS MinDate FROM yourTable GROUP BY id#
) AS M ON A.id# = M.id# AND A.Date = M.MinDate

However...when there are dupes of the same min date I get them in result which I wouldn't mind gettin rid of if anyone can assist.

source table:

id# sourceID Date
1 a1 2-3-02
2 c4 3-7-01
2 d3 4-12-98
3 5f 6-3-95
4 d5 3-11-97
4 2w 3-11-97
4 3v 1-15-01
5 3a 2-17-03


duped result:

id# sourceID Date
1 a1 2-3-02
2 d3 4-12-98
3 5f 6-3-95
4 d5 3-11-97
4 2w 3-11-97
5 3a 2-17-03

 

[PHV]

SELECT A.id#, Min(A.sourceID), A.Date
FROM yourTable AS A INNER JOIN (
SELECT id#, Min(Date) AS MinDate FROM yourTable GROUP BY id#
) AS M ON A.id# = M.id# AND A.Date = M.MinDate
GROUP BY A.id#, A.Date

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