[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on Sep 29, 2011 as part of a series I called ‘SQL Tips’ for coworkers based on an email distribution list I sent out before the internal blog. I have left this as originally written but have updated formatting for my WordPress theme. Note: there are multiple ways to do this but at the time I originally wrote this I only wrote about this one.
I’ve been asked this a couple times over the past few months and again today so I thought I’d send it out in hopes that others will benefit from this.
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.
SELECT tbl.MachineName ,tbl.DateColumn ,tbl.Column3 –,tbl…. FROM Table1 tbl INNER JOIN ( SELECT MachineName AS [MachineName] ,MAX(DateColumn) AS [MxDt] FROM Table1 GROUP BY MachineName ) sub ON tbl.MachineName = sub.MachineName AND tbl.DateColumn = sub.MxDt;