SQL Tip: Return only latest record by date

[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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s