help with distinct rows and data ordering

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

Answers


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

Need Your Help

Implementing a different Kernel for 2D Kernel Density Estimation in R

r statistics kernel-density

I'm looking for some help understanding how to implement a 2-dimensional kernel density method, with a isotropic variance, and a bivariate normal kernel, kind of, but instead of using the typical

time data '2015-08-19 11:11:51 %z' does not match format '%Y-%m-%d %H:%M:%S +0000'

python django datetime

I wish to convert one time zone(GMT) to another (IST) using python.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.