SQL Tip: Return only latest record by date


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.

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;

Comments (0)

Skip to main content