help with distinct rows and data ordering

Question

If I have records:

Row  Date,        LocationID, Account
1    Jan 1, 2008  1           1000
2    Jan 2, 2008  1           1000
3    Jan 3, 2008  2           1001
4    Jan 3, 2008  1           1001
5    Jan 3, 2008  3           1001
6    Jan 4, 2008  3           1002

I need to get the row (date, locatinid, account) where the row has the most recent date for each distinct locationid:

4    Jan 3, 2008  1           1001
3    Jan 3, 2008  2           1001
6    Jan 4, 2008  3           1002

Answer 1

Try something like:

select *
from mytable t1
where date = (select max(date) from mytable t2
              where t2.location = t1.location);

Answer 2

select t.* from mytable t,
(select max(Date) as Date,LocationID from mytable group by LocationID) t1 
where t.Date = t1.Date and t.LocationID = t1.LocationID 
order by t1.LocationID

Answer 3

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
  ON (t1.locationid = t2.locationid 
    AND (t1.date < t2.date OR t1.date = t2.date AND t1.row < t2.row))
WHERE t2.row IS NULL;

This solution returns only one row per locationid, even if there are multiple rows with the same max date.

Answer 4

I think this would work:

SELECT t1.*
FROM table t1
  JOIN (SELECT MAX(Date), LocationID
        FROM table
        GROUP BY Date, LocationID) t2 on t1.Date = t2.Date and t1.LocationID = t2.LocationID