Selecting the most recent date on a joined table

Question

I cannot figure out how to create a sub-query to select only the latest date of the grouped by value sys_loc_code. Two tables E and R joined on sys_sample_code. I want to to the distinct value for the sys_loc_code field. I want this to be the data from the row that contains the latest date in it's sample_date field.

The code I have so far is:

SELECT E.sample_date, E.sys_loc_code, R.sys_sample_code, R.chemical_name, R.result_value, R.detect, R.LabSampType
FROM GMP.GMP_Sample_Results AS R
INNER JOIN GMP.GMP_Sample_Events AS E ON R.sys_sample_code = E.sys_sample_code
WHERE (R.chemical_name = N'Tetrachloroethene') and E.sample_date > '2016-01-01 00:00:00.000'
ORDER BY sys_loc_code, sample_date desc

Please see image for desired results. Desired results are in yellow.

I have tried MAX, DISTINCT, multiple joins, MAX DISTINCT, GROUP BY and countless others. Can someone please suggest the code I need to get the results I desire. Many thanks.

Answer 1

If you use ROW_NUMBER and PARTITION BY the column you want to be unique and ORDER BY the column you want the most recent of, and then take only those results where the row number is 1, you should get what you want.

SELECT sample_date, sys_loc_code, sys_sample_code, chemical_name, result_value, detect, LabSampType
FROM (
    SELECT E.sample_date, E.sys_loc_code, R.sys_sample_code, R.chemical_name, R.result_value, R.detect, R.LabSampType
      , ROW_NUMBER() OVER (PARTITION BY E.sys_loc_code ORDER BY sample_date DESC) RN
    FROM GMP.GMP_Sample_Results AS R
    INNER JOIN GMP.GMP_Sample_Events AS E ON R.sys_sample_code = E.sys_sample_code
    WHERE (R.chemical_name = N'Tetrachloroethene') AND E.sample_date > '2016-01-01 00:00:00.000'
) X
WHERE RN = 1
ORDER BY sys_loc_code, sample_date DESC;