This was originally created in 2011/2012 in a series I dubbed "SQL Tips" for my team (via email and an internal blog site). I've decided to add these SQL Tips to my external blog. These are being posted exactly how they were written ~6 years ago...unless I found someone's name being called out...I may have edited that to protect the innocent. 🙂
Note: there are multiple ways to do this but at the time I originally wrote this I only wrote about this one.
Consider the following:
- You have a table that can have multiple records for a given entity (such as a machine)
- The table has a date specifying the time of the record insert (or something like an inventory scan time)
- You don’t want to see multiple records for a given machine; meaning, you want to only return one record per machine
- The record you want to see is the one with the latest information (determined by the date column)
The following generalized query will allow you to do this.
FROM Table1 tbl
INNER JOIN (
SELECT MachineName AS [MachineName]
,MAX(DateColumn) AS [MxDt]
GROUP BY MachineName
ON tbl.MachineName = sub.MachineName
AND tbl.DateColumn = sub.MxDt;