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

Revision: 59522

at September 14, 2012 02:21 by RobertKaucher

Initial Code

DECLARE @A TABLE
(
part_no VARCHAR(5),
rev CHAR,
on_hand TINYINT,
safety_stock TINYINT,
so_no VARCHAR(5),
so_date DATETIME
)


INSERT @A
SELECT '12345', 'A', 10, 15, 'S1234', '12/14/2009' UNION ALL
SELECT '12345', 'A', 10, 15, 'S1233', '10/01/2009' UNION ALL
SELECT '12345', 'A', 10, 15, 'S1232', '08/02/2009' UNION ALL
SELECT '12346', '',  5, 0, 'S1231', '08/01/2009' UNION ALL
SELECT '12347', '-', 0, 0, 'S1230', '10/20/2009' UNION ALL
SELECT '12347', '-', 0, 0, 'S1229', '07/15/2009'

SELECT * FROM @A AS A
WHERE so_date =
(
    SELECT MAX(so_date)
    FROM @A AS B
    WHERE B.part_no = A.part_no AND B.Rev = A.Rev
)

Initial URL

Initial Description

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

Initial Title

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

Initial Tags

Initial Language

C#

Choose a language for easy browsing: