Select unique barcode with max timestamp

Question

I'm trying to write an SQL query using Microsoft SQL Server.

My table looks similar to this:

bardcode |  products |      timestamp

1234     |  12       | 2013-02-19 00:01:00
1234     |  17       | 2013-02-19 00:01:00
432      |  10       | 2013-02-19 00:01:00
432      |   3       | 2013-02-19 00:02:00
643      |  32       | 2013-02-19 00:03:00
643      |   3       | 2013-02-19 00:03:00
123      |  10       | 2013-02-19 00:04:00

I'm trying to get a list of unique barcodes based on the most recent timestamps.

Here is my not-working query I've been thinking of:

    SELECT DISTINCT [Barcode], [Timestamp]
    FROM [InventoryLocatorDB].[dbo].[Inventory]
    WHERE max([Timestamp])

EDIT I'd like to retain the additional columns as well. Do I do a join or someting.

For example I want to select the the barcode with the latest timestamp and would like all of the other column information to come with it e.g. products column

This is a very popular question on SO, there's tons of examples of it. Here's [One example](http://stackoverflow.com/questions/1140064/sql-query-to-get-most-recent-row-for-each-instance-of-a-given-key/1140091#1140091) — Andrew, Oct 13 '14 at 14:10
Thanks its just hard to parse when they aren't specific enough and I'm still inexperienced. — visc, Oct 13 '14 at 14:15

manlio · Accepted Answer · 2014-10-14T08:29:58.577

This should work:

SELECT   [Barcode], max([TimeStamp])
FROM     [InventoryLocatorDB].[dbo].[Inventory]
GROUP BY [Barcode]

Demo

EDIT

SELECT [Barcode], [Products], [TimeStamp]
FROM   [InventoryLocatorDB].[dbo].[Inventory] AS I
WHERE  [TimeStamp] = (SELECT MAX([TimeStamp])
                      FROM   [InventoryLocatorDB].[dbo].[Inventory]
                      WHERE  [Barcode] = I.[Barcode])

The query retains tuples with the same BarCode / TimeStamp. Depending on the granularity of TimeStamp this may not be valid.

Demo 2

There are many ways to "filter" the above result.

E.g. only one tuple per BarCode, latest TimeStamp, greatest value of Products:

SELECT [Barcode], [Products], [TimeStamp]
FROM   [InventoryLocatorDB].[dbo].[Inventory] AS I
WHERE  [TimeStamp] = (SELECT MAX([TimeStamp])
                      FROM   [InventoryLocatorDB].[dbo].[Inventory]
                      WHERE  [Barcode] = I.[Barcode]) AND
       [Products]  = (SELECT MAX([Products])
                      FROM   [InventoryLocatorDB].[dbo].[Inventory]
                      WHERE  [Barcode] = I.[Barcode] and [TimeStamp] = I.[TimeStamp])

Demo 3

Select unique barcode with max timestamp

1 Answers1