select a distinct row in left join

Question

Table 1

ID
1
2
3

Table 2

ID    date    opt
1    1/1/10    1
1    1/2/10    0
2    1/1/10    1

I Want

ID    date    opt
1    1/2/10    0
2    1/1/10    1

How do I join these 2 tables? Just match all the ID's in table 1 with their most recent opt in table 2. Without partitions, please. I'm in sql 2005. Thanks.

Answer 1

Just forget it.

Answer 2

select t1.ID, t2.date, t2.opt
from (
    select ID, max(date) as MaxDate
    from Table2 
    group by ID
) t2m
inner join Table2 t2 on t2m.ID = t2.ID and t2.MaxDate = t2.date
inner join Table1 t1 on t2.ID = t1.ID

Answer 3

Select ID, date, opt
From Table2 As T2
Where date = (
            Select Max(date)
            From Table2 As T3
            Where T3.ID = T.ID
            )

Answer 4

Here's the solution without subqueries:

SELECT t1.ID, t2.date, t2.opt
FROM Table1 t1
LEFT JOIN Table2 t2
  ON t2.ID = t1.ID
LEFT JOIN Table2 t3
  ON t3.ID = t1.ID AND t3.date > t2.date
WHERE t3.date IS NULL

You could get duplicates if you have two entries in table 2 with the same "latest" date and ID. However, you could add additional conditions to handle two entries with the same "latest" date. Also, you will get NULL values for date and opt if there is no corresponding record in table 2.

There is a whole chapter about this type of type of solution in the book SQL Antipatterns.