Clever Geek Handbook

Help with single lines and data ordering

If I have entries:

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 a string ( date

, locatinid

, account

), where the string has the most recent date for each individual locationid

:

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


source to share


4 answers


I think this will 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
+2


source


Try something like:



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


source 


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
0


source 


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 only returns one row for each location, even if there are multiple rows with the same maximum date.

0


source






More articles:


All Articles