# Posted By

bvwatson on 05/14/15

# Statistics

Viewed 450 times
Favorited by 0 user(s)

# Find last two updates to a record

/ Published in: MySQL

Created as reply to question on ittoolbox.com:"Need your help in writing a query to pull audit history i.e. Last updated by a user 'XYZ' and for the same record key (ID) the second last record in the audit history table. i.e. Last update by this user and the previous updated record to this record by anybody e.g. "
http://database.ittoolbox.com/groups/technical-functional/sql-l/sql-to-find-records-updated-by-particular-user-and-the-prior-one-5732386

This snippet is written to show a step-by-step process of building the query to produce the results. It started with the simple query, then builds on that to the final query and results.

Copy this code and paste it in your HTML
`-- Let's restate the problem and solve it in pieces. Then put the pieces together. -- CREATE THE DATA create table theTable (  id int,  updated_by varchar(20),  update_date date);begin work;insert into theTable (id, updated_by, update_date) VALUES (1,"XYZ",DATE "2015-05-11"); insert into theTable (id, updated_by, update_date) VALUES (1,"ABC",DATE "2015-05-10"); insert into theTable (id, updated_by, update_date) VALUES (1,"CDE",DATE "2015-05-09"); insert into theTable (id, updated_by, update_date) VALUES (2,"ABC",DATE "2015-05-11"); insert into theTable (id, updated_by, update_date) VALUES (2,"XYZ",DATE "2015-05-10"); insert into theTable (id, updated_by, update_date) VALUES (3,"XYZ",DATE "2015-05-11"); insert into theTable (id, updated_by, update_date) VALUES (3,"XYZ",DATE "2015-05-10"); insert into theTable (id, updated_by, update_date) VALUES (3,"CDE",DATE "2015-05-09"); commit; -- WHAT'S IN THE TABLE? select id, updated_by, update_date from theTable; -- (1) WHEN WAS EACH ROW MOST RECENTLY UPDATED? select id, max(update_date) as "max_update_date" from theTable group by id; -- (2) WHO MADE THAT UPDATE? select id, updated_by, update_date from theTablewhere (id, update_date) in (select id, max(update_date) as "max_update_date" from theTable group by id); -- (3) WHEN WERE ALL THE PREVIOUS UPDATES FOR THAT ROW? select secondUpdate.id, secondUpdate.update_date from theTable secondUpdatejoin (select id, max(update_date) as "max_update_date" from theTable group by id) lastUpdateon secondUpdate.id = lastUpdate.idand secondUpdate.update_date < lastUpdate.max_update_date; -- (4) WHAT'S THE MOST RECENT OF THOSE PREVIOUS UPDATES? select secondUpdate.id, max(secondUpdate.update_date) max_previous_datefrom theTable secondUpdatejoin (select id, max(update_date) as "max_update_date" from theTable group by id) lastUpdateon  secondUpdate.id = lastUpdate.idand secondUpdate.update_date < lastUpdate.max_update_dategroup by secondUpdate.id; -- (5) WHO MADE THAT LAST PREVIOUS UPDATE? select T.id,T.updated_by, T.update_datefrom theTable Tjoin (select secondUpdate.id, max(secondUpdate.update_date) max_previous_datefrom theTable secondUpdatejoin (select id, max(update_date) as "max_update_date" from theTable group by id) lastUpdateon  secondUpdate.id = lastUpdate.idand secondUpdate.update_date < lastUpdate.max_update_dategroup by secondUpdate.id) lastPreviousUpdateon T.id = lastPreviousUpdate.idand T.update_date = lastPreviousUpdate.max_previous_date; -- (6) NOW GET BOTH OF THEM select L.id "Record", L.updated_by "lastUpdater", L.update_date "lastUpdate", P.updated_by "prevUpdater", P.update_date "prevUpdate"from (  select id, updated_by, update_date   from theTable  where (id, update_date) in (  select id, max(update_date) as "max_update_date"   from theTable   group by id  )) LLEFT JOIN(  select T.id, T.updated_by, T.update_date  from theTable T  join (    select secondUpdate.id, max(secondUpdate.update_date) max_previous_date    from theTable secondUpdate    join (      select id, max(update_date) as "max_update_date"       from theTable       group by id      ) lastUpdate        on  secondUpdate.id = lastUpdate.id        and secondUpdate.update_date < lastUpdate.max_update_date        group by secondUpdate.id    ) lastPreviousUpdate        on T.id = lastPreviousUpdate.id        and T.update_date = lastPreviousUpdate.max_previous_date) Pon L.id = P.idorder by L.id;`