Tutorial :select a distinct row in left join


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.


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


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 solution in the book SQL Antipatterns.


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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »