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;

3 thoughts on “SQL Tip: Return only latest record by date

    1. That would be easy…but it wouldn’t be correct in this scenario. The reason for that is: if you add the “t.xxxx” you’ll likely get multiple records for the machinename. Which is why by first getting the machinename and it’s max date and then joining to itself you can get the correct results. Obviously, if you just needed the latest date then it would be pretty simple…well, even simpler than the example I gave. There are a few ways to achieve this, I only showed one example. Does this explanation make more sense?

      Like

      1. I put this little example together for you Doug…copy/paste this and see what I mean:

        DECLARE @Table1 table ( MachineName varchar(25) NOT NULL
        ,Column3 varchar(25) NULL
        ,DateColumn datetime NOT NULL
        );
        INSERT @Table1
        VALUES (‘MachineOne’,’some data’,’2019-08-09 20:18:31.270′),(‘MachineOne’,’somedata’,’2019-09-09 19:18:31.270′)
        ,(‘MachineOne’,’hello’,’2019-09-08 20:18:31.270′),(‘MachineOne’,’hi’,’2019-09-09 20:18:31.270′)
        ,(‘MachineTwo’,’blah’,’2019-09-09 14:34:31.270′),(‘MachineTwo’,’blah’,’2019-09-08 14:34:31.270′)
        ,(‘MachineTwo’,’blank’,’2019-09-09 03:18:31.270′),(‘MachineThree’,’Something’,’2019-09-07 15:18:31.270′)
        ,(‘MachineThree’,’Something’,’2019-09-09 15:18:31.270′);

        — Working example:
        SELECT tbl.MachineName
        ,tbl.DateColumn
        ,tbl.Column3
        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;

        — Non working example:
        SELECT MachineName
        ,MAX(DateColumn)
        ,Column3
        FROM @Table1
        GROUP BY MachineName
        ,Column3;

        Like

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