SQL2005 + return distinct result based on datetime on table in inner join

Question

Im having a moment, what i want to do is really simple and i think im just looking at the wrong solution. I want to simply return a table that is sorted based on a datestamp of a related table.

ie:

Table 1:
200   MyStuff   OK
201   Other     Why
202   Flat      So

Table 2:
1  200  5/12/2009  MyValue1
2  200  5/11/2009  MyValue2
3  201  7/10/2009  MyValue3
4  201  7/08/2009  MyValue4

I want the to return the first table sorted based on the dates in the second table, so my result should be

201  Other   Why
200  MyStuff OK
202  Flat    So

I have tried doing an inner join, but what i select distinct i have to include the date from the second table that im sorting on, thus i end up with duplicate pk's on the return table.

Please help me understand my rookie mistake here.

Answer 1

Since you can have multiple rows in Table2 for every row in Table1, you'll have to decide on how to handle the dates as far as sorting.

Let's say you want to sort on the maximum date stamp, you'd do something like:

select table1.*, t2.max_ds

from table1

inner join (select id, max(datestamp) as max_ds from table2 group by id) t2

on t2.id = table1.id

order by t2.max_ds

You'll obviously have to add some null handling and whatnot, but that should get you started.