Tsiftis Karampouzouklis Tsiftis Karampouzouklis - 1 month ago 22
SQL Question

select the most recent entry

I have the following table:

LOCATION_ID, PERSON_ID, DATE
3, 65, 2016-06-03
7, 23, 2016-10-28
3, 23, 2016-08-05
5, 65, 2016-07-14


I want to build a select query in PL/SQL to select the records with the most recent location_id per person_id. For the above sample, the desired result should be:

LOCATION_ID, PERSON_ID, DATE
5, 65, 2016-07-14
7, 23, 2016-10-28


(DATE expressed as 'YYYY-MM-DD')

Thank you!

Answer

You can first extract the most recent event for each person by grouping results by PERSON_ID and selecting the MAX(DATE).

Then join the table with itself on those two columns to retrieve the LOCATION_ID

SELECT
  YOUR_TABLE.LOCATION_ID,
  YOUR_TABLE.PERSON_ID,
  YOUR_TABLE.DATE
FROM
  (SELECT
    PERSON_ID, MAX(DATE) AS max_date
  FROM
    YOUR_TABLE
  GROUP BY
    PERSON_ID
  ) AS t1
LEFT JOIN
  YOUR_TABLE
ON
  YOUR_TABLE.PERSON_ID = t1.PERSON_ID
  AND
  YOUR_TABLE.DATE = t1.max_date

By the way, you should not use reserved words like DATE for column names.

Here is fiddle to show it working: http://sqlfiddle.com/#!9/efdcb/2