帮助完成不同的行和数据排序(help with distinct rows and data ordering)

帮助完成不同的行和数据排序(help with distinct rows and data ordering)

如果我有记录：

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

我需要获取行（ date ， locatinid ， account ）行中每个不同的locationid最近的日期：

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

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

最满意答案

我认为这将工作：

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

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