CODEWITHSUNDEEP
sqlsql-server-2008
Rod
Rod

Reputation: 15457

get earliest date for each id

given the following 

table1
id,name
1,test1
2,test2
3,test3

tablePrt
id,date
1,5/1/11
1,5/3/11
3,3/1/11

tableOnl
id,date
1,5/4/11
1,5/15/11

tableSrv
id,date
1,5/13/11
1,5/1/11
2,4/1/11

what are some ways to get the single earliest date for each id from all 3 tables plus the name of the id that's in table1?

so the result from above should look like the following:

test1, 5/1/11
test2, 4/1/11
test3, 3/1/11

Upvotes: 1

Views: 2618

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

SELECT t1.id, 
       MIN(tbls.DATE) 
FROM   table1 t1 
       INNER JOIN (SELECT id, 
                          DATE 
                   FROM   tableprt 
                   UNION ALL
                   SELECT id, 
                          DATE 
                   FROM   tableonl 
                   UNION ALL
                   SELECT id, 
                          DATE 
                   FROM   tablesrv) tbls 
         ON t1.id = tbls.id 
GROUP  BY t1.id

Note: no need to do UNION ALL here As Martin Noted Even though UNION ALL isn't required it may be faster

Upvotes: 3

Related Questions