Posted By

bvwatson on 05/14/15

Tagged

sql
date

Versions (?)

Last Edited at 05/14/15 03:59am

Statistics

Viewed 825 times

Favorited by 0 user(s)

Related snippets

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.

Expand | Embed | Plain Text

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 theTable
where (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 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
;
 
-- (4) WHAT'S THE MOST RECENT OF THOSE PREVIOUS UPDATES?
 
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;
 
-- (5) WHO MADE THAT LAST PREVIOUS UPDATE?
 
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
;
 
-- (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
  )
) L
LEFT 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
) P
on L.id = P.id
order by L.id
;