thaynes thaynes - 19 days ago 5
SQL Question

SQL Where clause variable needs to differ per row

Given the following data:

ID, Name, Location, Date
1, Tom, Boston, 8/12/2015
1, Tom, Lowell, 7/12/2015
1, Tom, Wakefield, 6/12/2015
2, Jake, Salem, 12/15/2018
2, Jack, Worcester, 5/12/2015


How would one write an SQL query which would return a table containing only the rows with largest dates, e.g.:

ID, Name, Location, Date
1, Tom, Boston, 8/12/2015
2, Jake, Salem, 12/15/2018


Assuming that
ID
and
Name
are fixed keys. However,
location
may be variable, the
location
that should be returned would be the one associated with the maximum
date
.

My closest attempt is currently only returning for the MAX of all dates, not the record with the max date for the
ID
/
Name
pair. e.g.:

SELECT ID, NAME, Location, Data
FROM Table A
WHERE Date = (SELECT MAX(Date) FROM Table B WHERE A.ID = B.ID and A.Name = B.Name)


Only yields:

2, Jake, Salem, 12/15/2018

Answer

Method 1:

SELECT A.* FROM [Table] A
JOIN (
    SELECT ID, Name, MAX([Date]) MaxDate
    FROM [Table]
    GROUP BY ID, Name
) B ON B.ID = A.ID AND B.Name = A.Name AND B.MaxDate = A.[Date]

Method 2:

SELECT ID, Name, [Location], [Date] FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY [Date] DESC) R
    FROM [Table]
) T
WHERE R = 1

Example:

DECLARE @T TABLE (
    ID INT,
    Name VARCHAR(16),
    [Location] VARCHAR(16),
    [Date] DATE
)

INSERT @T VALUES
(1, 'Tom', 'Boston', '8/12/2015'),
(1, 'Tom', 'Lowell', '7/12/2015'),
(1, 'Tom', 'Wakefield', '6/12/2015'),
(2, 'Jake', 'Salem', '12/15/2018'),
(2, 'Jack', 'Worcester', '5/12/2015')

SELECT A.* FROM @T A
JOIN (
    SELECT ID, Name, MAX([Date]) MaxDate
    FROM @T
    GROUP BY ID, Name
) B ON B.ID = A.ID AND B.Name = A.Name AND B.MaxDate = A.[Date]

SELECT ID, Name, [Location], [Date] FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY [Date] DESC) R
    FROM @T
) T
WHERE R = 1