Posted By

bvwatson on 05/14/15


Tagged

sql date last next-to-last


Versions (?)

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.

  1. -- Let's restate the problem and solve it in pieces. Then put the pieces together.
  2.  
  3. -- CREATE THE DATA
  4.  
  5. CREATE TABLE theTable (
  6. id INT,
  7. updated_by VARCHAR(20),
  8. update_date DATE
  9. );
  10. BEGIN work;
  11. INSERT INTO theTable (id, updated_by, update_date) VALUES (1,"XYZ",DATE "2015-05-11");
  12. INSERT INTO theTable (id, updated_by, update_date) VALUES (1,"ABC",DATE "2015-05-10");
  13. INSERT INTO theTable (id, updated_by, update_date) VALUES (1,"CDE",DATE "2015-05-09");
  14. INSERT INTO theTable (id, updated_by, update_date) VALUES (2,"ABC",DATE "2015-05-11");
  15. INSERT INTO theTable (id, updated_by, update_date) VALUES (2,"XYZ",DATE "2015-05-10");
  16. INSERT INTO theTable (id, updated_by, update_date) VALUES (3,"XYZ",DATE "2015-05-11");
  17. INSERT INTO theTable (id, updated_by, update_date) VALUES (3,"XYZ",DATE "2015-05-10");
  18. INSERT INTO theTable (id, updated_by, update_date) VALUES (3,"CDE",DATE "2015-05-09");
  19. COMMIT;
  20.  
  21. -- WHAT'S IN THE TABLE?
  22.  
  23. SELECT id, updated_by, update_date FROM theTable;
  24.  
  25. -- (1) WHEN WAS EACH ROW MOST RECENTLY UPDATED?
  26.  
  27. SELECT id, max(update_date) as "max_update_date"
  28. FROM theTable
  29. GROUP BY id;
  30.  
  31. -- (2) WHO MADE THAT UPDATE?
  32.  
  33. SELECT id, updated_by, update_date
  34. FROM theTable
  35. WHERE (id, update_date) IN (
  36. SELECT id, max(update_date) as "max_update_date"
  37. FROM theTable
  38. GROUP BY id
  39. );
  40.  
  41. -- (3) WHEN WERE ALL THE PREVIOUS UPDATES FOR THAT ROW?
  42.  
  43. SELECT secondUpdate.id, secondUpdate.update_date
  44. FROM theTable secondUpdate
  45. JOIN (
  46. SELECT id, max(update_date) as "max_update_date"
  47. FROM theTable
  48. GROUP BY id
  49. ) lastUpdate
  50. on secondUpdate.id = lastUpdate.id
  51. AND secondUpdate.update_date < lastUpdate.max_update_date
  52. ;
  53.  
  54. -- (4) WHAT'S THE MOST RECENT OF THOSE PREVIOUS UPDATES?
  55.  
  56. SELECT secondUpdate.id, max(secondUpdate.update_date) max_previous_date
  57. FROM theTable secondUpdate
  58. JOIN (
  59. SELECT id, max(update_date) as "max_update_date"
  60. FROM theTable
  61. GROUP BY id
  62. ) lastUpdate
  63. on secondUpdate.id = lastUpdate.id
  64. AND secondUpdate.update_date < lastUpdate.max_update_date
  65. GROUP BY secondUpdate.id;
  66.  
  67. -- (5) WHO MADE THAT LAST PREVIOUS UPDATE?
  68.  
  69. SELECT T.id,T.updated_by, T.update_date
  70. FROM theTable T
  71. JOIN (
  72. SELECT secondUpdate.id, max(secondUpdate.update_date) max_previous_date
  73. FROM theTable secondUpdate
  74. JOIN (
  75. SELECT id, max(update_date) as "max_update_date"
  76. FROM theTable
  77. GROUP BY id
  78. ) lastUpdate
  79. on secondUpdate.id = lastUpdate.id
  80. AND secondUpdate.update_date < lastUpdate.max_update_date
  81. GROUP BY secondUpdate.id
  82. ) lastPreviousUpdate
  83. on T.id = lastPreviousUpdate.id
  84. AND T.update_date = lastPreviousUpdate.max_previous_date
  85. ;
  86.  
  87. -- (6) NOW GET BOTH OF THEM
  88.  
  89. SELECT L.id "Record", L.updated_by "lastUpdater", L.update_date "lastUpdate", P.updated_by "prevUpdater", P.update_date "prevUpdate"
  90. FROM
  91. (
  92. SELECT id, updated_by, update_date
  93. FROM theTable
  94. WHERE (id, update_date) IN (
  95. SELECT id, max(update_date) as "max_update_date"
  96. FROM theTable
  97. GROUP BY id
  98. )
  99. ) L
  100. LEFT JOIN
  101. (
  102. SELECT T.id, T.updated_by, T.update_date
  103. FROM theTable T
  104. JOIN (
  105. SELECT secondUpdate.id, max(secondUpdate.update_date) max_previous_date
  106. FROM theTable secondUpdate
  107. JOIN (
  108. SELECT id, max(update_date) as "max_update_date"
  109. FROM theTable
  110. GROUP BY id
  111. ) lastUpdate
  112. on secondUpdate.id = lastUpdate.id
  113. AND secondUpdate.update_date < lastUpdate.max_update_date
  114. GROUP BY secondUpdate.id
  115. ) lastPreviousUpdate
  116. on T.id = lastPreviousUpdate.id
  117. AND T.update_date = lastPreviousUpdate.max_previous_date
  118. ) P
  119. on L.id = P.id
  120. ORDER BY L.id
  121. ;

Report this snippet  

You need to login to post a comment.