Advanced Search

Using MAX(DATETIME) and Sub Queries to Find Most Recent Item

/ Published in: C#
Save to your folder(s)
Using `MAX(DATETIME)` and Sub Queries to Find Most Recent Item

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE @A TABLE
  2. (
  3. part_no VARCHAR(5),
  4. rev CHAR,
  5. on_hand TINYINT,
  6. safety_stock TINYINT,
  7. so_no VARCHAR(5),
  8. so_date DATETIME
  9. )
  10.  
  11.  
  12. INSERT @A
  13. SELECT '12345', 'A', 10, 15, 'S1234', '12/14/2009' UNION ALL
  14. SELECT '12345', 'A', 10, 15, 'S1233', '10/01/2009' UNION ALL
  15. SELECT '12345', 'A', 10, 15, 'S1232', '08/02/2009' UNION ALL
  16. SELECT '12346', '',  5, 0, 'S1231', '08/01/2009' UNION ALL
  17. SELECT '12347', '-', 0, 0, 'S1230', '10/20/2009' UNION ALL
  18. SELECT '12347', '-', 0, 0, 'S1229', '07/15/2009'
  19.  
  20. SELECT * FROM @A AS A
  21. WHERE so_date =
  22. (
  23.     SELECT MAX(so_date)
  24.     FROM @A AS B
  25.     WHERE B.part_no = A.part_no AND B.Rev = A.Rev
  26. )

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.