Retrieving the most recent records within a query
I have the following tables:
tblPerson:PersonID | Name
---------------------
1 | 开发者_如何学GoJohn Smith
2 | Jane Doe
3 | David Hoshi
tblLocation:
LocationID | Timestamp | PersonID | X | Y | Z | More Columns...
---------------------------------------------------------------
40 | Jan. 1st | 3 | 0 | 0 | 0 | More Info...
41 | Jan. 2nd | 1 | 1 | 1 | 0 | More Info...
42 | Jan. 2nd | 3 | 2 | 2 | 2 | More Info...
43 | Jan. 3rd | 3 | 4 | 4 | 4 | More Info...
44 | Jan. 5th | 2 | 0 | 0 | 0 | More Info...
I can produce an SQL query that gets the Location records for each Person like so:
SELECT LocationID, Timestamp, Name, X, Y, Z
FROM tblLocation
JOIN tblPerson
ON tblLocation.PersonID = tblPerson.PersonID;
to produce the following:
LocationID | Timestamp | Name | X | Y | Z |
--------------------------------------------------
40 | Jan. 1st | David Hoshi | 0 | 0 | 0 |
41 | Jan. 2nd | John Smith | 1 | 1 | 0 |
42 | Jan. 2nd | David Hoshi | 2 | 2 | 2 |
43 | Jan. 3rd | David Hoshi | 4 | 4 | 4 |
44 | Jan. 5th | Jane Doe | 0 | 0 | 0 |
My issue is that we're only concerned with the most recent Location record. As such, we're only really interested in the following Rows: LocationID 41, 43, and 44.
The question is: How can we query these tables to give us the most recent data on a per-person basis? What special grouping needs to happen to produce the desired result?
MySQL doesn't have ranking/analytical/windowing functionality.
SELECT tl.locationid, tl.timestamp, tp.name, X, Y, Z
FROM tblPerson tp
JOIN tblLocation tl ON tl.personid = tp.personid
JOIN (SELECT t.personid,
MAX(t.timestamp) AS max_date
FROM tblLocation t
GROUP BY t.personid) x ON x.personid = tl.personid
AND x.max_date = tl.timestamp
SQL Server 2005+ and Oracle 9i+ support analytics, so you could use:
SELECT x.locationid, x.timestamp, x.name, x.X, x.Y, x.Z
FROM (SELECT tl.locationid, tl.timestamp, tp.name, X, Y, Z,
ROW_NUMBER() OVER (PARTITION BY tp.name ORDER BY tl.timestamp DESC) AS rank
FROM tblPerson tp
JOIN tblLocation tl ON tl.personid = tp.personid) x
WHERE x.rank = 1
Using a variable to get same as ROW_NUMBER functionality on MySQL:
SELECT x.locationid, x.timestamp, x.name, x.X, x.Y, x.Z
FROM (SELECT tl.locationid, tl.timestamp, tp.name, X, Y, Z,
CASE
WHEN @name != t.name THEN
@rownum := 1
ELSE @rownum := @rownum + 1
END AS rank,
@name := tp.name
FROM tblLocation tl
JOIN tblPerson tp ON tp.personid = tl.personid
JOIN (SELECT @rownum := NULL, @name := '') r
ORDER BY tp.name, tl.timestamp DESC) x
WHERE x.rank = 1
As @Mark Byers mentions, this problem comes up frequently on Stack Overflow.
Here's the solution I most frequently recommend, given your tables:
SELECT p.*, l1.*
FROM tblPerson p
JOIN tblLocation l1 ON p.PersonID = l1.PersonID
LEFT OUTER JOIN tblLocation l2 ON p.PersonID = l2.PersonID AND
(l1.timestamp < l2.timestamp OR l1.timestamp = l2.timestamp AND l1.LocationId < l2.LocationId)
WHERE l2.LocationID IS NULL;
To see other examples, follow the tag greatest-n-per-group
, which I added to your question.
This is a classic 'max per group' question that comes up on Stack Overflow almost every day. There are many ways to solve it and you can find example solutions by searching Stack Overflow. Here is one way that you can do it in MySQL:
SELECT
location.LocationId,
location.Timestamp,
person.Name,
location.X,
location.Y,
location.Z
FROM (
SELECT
LocationID,
@rn := CASE WHEN @prev_PersonID = PersonID
THEN @rn + 1
ELSE 1
END AS rn,
@prev_PersonID := PersonID
FROM (SELECT @prev_PersonID := NULL) vars, tblLocation
ORDER BY PersonID, Timestamp DESC
) T1
JOIN tblLocation location ON location.LocationID = T1.LocationId
JOIN tblPerson person ON person.PersonID = location.PersonID
WHERE rn = 1
精彩评论