Selecting only most recent date for attributes in SQL

Question

I have a very large historical dataset, and I would like to get a table that shows only the result for the most recent date for each location.

How can I get it to spit out the results only for the most recent date in the dataset?

My code to get all the data is as follows:

SELECT StationName, CONVERT(date, SampleDate_D) AS SampleDate, LongName, Value, ReportingUnits, FlagCode, 
    DetectedResult, SampleMatrix
FROM Analyses AS a
LEFT JOIN Samples AS s ON s.SampleNumber=a.SampleNumber
LEFT JOIN SampleMatrix AS sm ON s.SampleMatrixCode=sm.SampleMatrixCode
LEFT JOIN Stations AS st ON st.StationNumber=s.StationNumber
LEFT JOIN Parameters AS p ON p.ParameterNumber=a.ParameterNumber
LEFT JOIN StationTypes AS stp ON stp.StationTypeCode=st.StationTypeCode
LEFT JOIN ReportingUnits AS ru ON ru.ReportingUnitsCode=a.ReportUnitsCode
WHERE LongName IN ('1,4-Dioxane', 'Chlorobenzene', '1,2-Dichlorobenzene', '1,3-Dichlorobenzene', '1,4-Dichlorobenzene', '1,2,3-Trichlorobenzene', '1,2,4-Trichlorobenzene', 
        '1,3,5-Trichlorobenzene', '1,2,3,4-Tetrachlorobenzene', '1,2,3,5-Tetrachlorobenzene', '1,3,4,5-Tetrachlorobenzene', 'Pentachlorobenzene', 'Hexachlorobenzene')
    AND QCSampleCode = 'O'
ORDER BY StationName, SampleDate_D;

Answer 1

Assuming the StationName determines the location, you can do this:

with numbered as (
    SELECT StationName, CONVERT(date, SampleDate_D) AS SampleDate, LongName, Value, ReportingUnits, FlagCode, 
        DetectedResult, SampleMatrix,
        row_number() OVER (PARTITION BY StationName ORDER BY CONVERT(date, SampleDate_D) DESC) rn
    FROM Analyses AS a
    LEFT JOIN Samples AS s ON s.SampleNumber=a.SampleNumber
    LEFT JOIN SampleMatrix AS sm ON s.SampleMatrixCode=sm.SampleMatrixCode
    LEFT JOIN Stations AS st ON st.StationNumber=s.StationNumber
    LEFT JOIN Parameters AS p ON p.ParameterNumber=a.ParameterNumber
    LEFT JOIN StationTypes AS stp ON stp.StationTypeCode=st.StationTypeCode
    LEFT JOIN ReportingUnits AS ru ON ru.ReportingUnitsCode=a.ReportUnitsCode
    WHERE LongName IN ('1,4-Dioxane', 'Chlorobenzene', '1,2-Dichlorobenzene', '1,3-Dichlorobenzene', '1,4-Dichlorobenzene', '1,2,3-Trichlorobenzene', '1,2,4-Trichlorobenzene', 
            '1,3,5-Trichlorobenzene', '1,2,3,4-Tetrachlorobenzene', '1,2,3,5-Tetrachlorobenzene', '1,3,4,5-Tetrachlorobenzene', 'Pentachlorobenzene', 'Hexachlorobenzene')
        AND QCSampleCode = 'O'
)
select *
from numbered
where rn = 1
order by StationName, SampleDate_D;

Answer 2

Use this as a sub-query or CTE and join it to all the other tables

SELECT StationName, MAX(CONVERT(date, SampleDate_D)) AS MaxSampleDate
    FROM Samples
    WHERE QCSampleCode = 'O'
    GROUP BY StationName

It might take some time, do you have indexes on those columns?