Currently I am performing a left join on two tables. The first table has an id and a persons name, the second table has an id, the id of a person from table 1, and then a timestamp (of a flight).
People Flights
id | name id | person_id | time
------------ ---------------------------
1 Dave 1 1 1284762115
2 Becky 2 1 1284787352
3 2 1284772629
4 2 1286432934
5 1 1283239480
When I perform my left join, I get a list of people and their flight times, but what I would like is just the list of people with the flight time with the highest ID
I have been using
SELECT p.id, p.name max(f.time)
FROM People p
LEFT JOIN Flights f ON p.id = f.person_id
GROUP BY p.id, p.name
However, this just gives me the LAST flight time, rather than the last flight time uploaded into the system (ie, highest ID).
1 Dave 1284787352
2 Becky 1286432934
So to reiterate, I would like to see the name of the person, along with the flight time of their last UPLOADED (highest ID) flight time.
1 Dave 1283239480
2 Becky 1286432934
Use:
SELECT p.id,
p.name,
f.time
FROM PEOPLE p
JOIN FLIGHTS f ON f.person_id = p.id
JOIN (SELECT f.person_id,
MAX(f.id) AS max_id
FROM FLIGHTS f
GROUP BY f.person_id) x ON x.person_id = f.person_id
AND x.max_id = f.id
If you are using a database that supports analytics:
SELECT p.id,
p.name,
x.time
FROM PEOPLE p
JOIN (SELECT f.person_id,
f.time,
ROW_NUMBER() OVER(PARTITION BY f.person_id
ORDER BY f.id DESC) AS rk
FROM FLIGHTS f) x ON x.person_id = p.id
AND x.rk = 1
If you want people, including those without flights:
SELECT p.id,
p.name,
f.time
FROM PEOPLE p
LEFT JOIN FLIGHTS f ON f.person_id = p.id
JOIN (SELECT f.person_id,
MAX(f.id) AS max_id
FROM FLIGHTS f
GROUP BY f.person_id) x ON x.person_id = f.person_id
AND x.max_id = f.id
...and the analytic version:
SELECT p.id,
p.name,
x.time
FROM PEOPLE p
LEFT JOIN (SELECT f.person_id,
f.time,
ROW_NUMBER() OVER(PARTITION BY f.person_id
ORDER BY f.id DESC) AS rk
FROM FLIGHTS f) x ON x.person_id = p.id
AND x.rk = 1