How can I SELECT distinct data based on a date field?

Question

I have table that stores a log of changes to objects in another table. Here are my table contents:

ObjID   Color   Date                     User
------- ------- ------------------------ --------
1       Red     2010-01-01 12:22:00.000  Joe
1       Blue    2010-01-02 15:22:00.000  Jill
1       Green   2010-01-03 16:22:00.000  Joe
1       White   2010-01-10 09:22:00.000  Mike
2       Red     2010-01-09 10:22:00.000  Mike
2       Blue    2010-01-12 09:22:00.000  Jill
2       Orange  2010-01-12 15:22:00.000  Joe

I want to select the most recent date for each Object, as well as the Color and User on the date of that record.

Bascically, I want this result set:

ObjID   Color   Date                     User
------- ------- ------------------------ --------
1       White   2010-01-10 09:22:00.000  Mike
2       Orange  2010-01-12 15:22:00.000  Joe

I'm having trouble wrapping my head around the SQL query I need to write to get this data...

I am retrieving data via ODBC from an iSeries DB2 database (AS/400).

Do you have a identity Pk on that table also? – Magnus May 25 '11 at 22:53 — Magnus, May 25 '11 at 22:53

Martin Smith · Answer 1 · 2011-05-25T23:20:05.203

Assuming at least SQL Server 2005

DECLARE @T TABLE (ObjID INT,Color VARCHAR(10),[Date] DATETIME,[User] VARCHAR(50))

INSERT INTO @T
SELECT 1,'Red',' 2010-01-01 12:22:00.000','Joe' UNION ALL
SELECT 1,'Blue','2010-01-02 15:22:00.000','Jill' UNION ALL
SELECT 1,'Green',' 2010-01-03 16:22:00.000','Joe' UNION ALL
SELECT 1,'White',' 2010-01-10 09:22:00.000','Mike' UNION ALL
SELECT 2,'Red',' 2010-01-09 10:22:00.000','Mike' UNION ALL
SELECT 2,'Blue','2010-01-12 09:22:00.000','Jill' UNION ALL
SELECT 2,'Orange','2010-01-12 15:22:00.000','Joe'

;WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY ObjID ORDER BY Date DESC) AS RN
FROM @T
)
SELECT ObjID,
       Color,
       [Date],
       [User]
FROM T 
WHERE RN=1

Or a SQL Server 2000 method from the article linked to in the comments

SELECT ObjID,
  CAST(SUBSTRING(string, 24, 33) AS VARCHAR(10)) AS Color,
  CAST(SUBSTRING(string,  1, 23) AS DATETIME ) AS [Date],
  CAST(SUBSTRING(string, 34, 83) AS  VARCHAR(50)) AS [User]
FROM 
(
SELECT ObjID, 
          MAX((CONVERT(CHAR(23), [Date], 126)
         + CAST(Color AS CHAR(10))
         + CAST([User] AS CHAR(50))) COLLATE Latin1_General_BIN) AS string
FROM @T
GROUP BY ObjID) T;

This is the only one that works if two dates on the same objID are the same. But depending on the size of the table it might not be optimal. — Magnus, May 25 '11 at 22:27
@Magnus - Some info on performance here http://www.sqlmag.com/article/departments/optimizing-top-n-per-group-queries — Martin Smith, May 25 '11 at 22:32
Even better would be to add a unique index on Obj1 + date. The date column should have precision enough. Or have a int identity pk column and do max on that instead (grouped by ObjID) — Magnus, May 26 '11 at 07:27

Nathan Tregillus · Accepted Answer · 2011-05-25T21:56:59.763

4

Hey there, I think you want the following (where ColorTable is your table name):

SELECT Color.* 
FROM ColorTable as Color
INNER JOIN 
(
SELECT ObjID, MAX(Date) as Date
FROM ColorTable
GROUP BY ObjID
) as MaxDateByColor
ON Color.ObjID = MaxDateByColor.ObjID
AND Color.Date = MaxDateByColor.Date

edited May 25 '11 at 21:56

answered May 25 '11 at 21:42

Nathan Tregillus

6,006
3
52
91

So basically, you are getting the max date per color within the sub-select, then joining those distinct combos to the full table, providing the entire rows results in the end result. – Nathan Tregillus May 25 '11 at 21:44
lol, story of my life. I think someone told me that when I was running for student council in high school :P – Nathan Tregillus May 25 '11 at 21:45
Ah! thanks martin. I tried to reply so fast, i did not notice his criteria is the object, not the color. >.< thanks, updating my answer now. – Nathan Tregillus May 25 '11 at 21:55

score 1 · Answer 3 · answered May 25 '11 at 23:32

If you have an Objects table and your ObjectHistory table has an index on ObjID and date, then this could perform better than other queries given so far:

SELECT
   X.*
FROM
   Objects O
   CROSS APPLY (
      SELECT TOP 1 *
      FROM ObjectHistory H
      WHERE O.ObjID = O.ObjID
      ORDER BY H.[Date] DESC
   ) X

The performance improvement may only come if you're pulling columns from the Objects table, too, but it's worth a shot.

If you want all Objects regardless of whether they have a history entry, switch to OUTER APPLY (and of course use O.ObjID instead of H.ObjID).

The neat thing about this query is that

It solves for situations where the Date value can have duplicates
It can support an arbitrary number of items per group (say, the top 5 instead of the top 1)

Date(time)Changed cannot have duplicates, IIUC SQL concurrency. — Tim, May 25 '11 at 23:49

score 0 · Answer 4 · answered Oct 22 '14 at 16:11

like martin smiths above, simply just do a row number over partition and pick one of the rows that is most recent like

SELECT  Color,Date,User
FROM (
   SELECT *,
          ROW_NUMBER() OVER (PARTITION BY User ORDER BY [DATE]) AS ROW_NUMBER
   FROM [tablename]
   ) AS ROWS
WHERE 
ROW_NUMBER = 2

score 0 · Answer 5 · edited May 23 '17 at 11:47

0

See these two related questions:

SQL/mysql - Select distinct/UNIQUE but return all columns?

And:

How to efficiently determine changes between rows using SQL

edited May 23 '17 at 11:47

Community

1
1

answered May 25 '11 at 21:43

Denis de Bernardy

75,850
13
131
154

score 0 · Answer 6 · answered May 25 '11 at 21:45

0

SELECT t1.* FROM Table_name as t1
INNER JOIN (
  SELECT MAX(Date) as MaxDate, ObjID FROM Table_name
  GROUP BY ObjID
) as t2
ON t1.ObjID = t2.ObjID AND t1.Date = t2.MaxDate

answered May 25 '11 at 21:45

Martijn

11,964
12
50
96

score 0 · Answer 7 · answered May 25 '11 at 22:08

You can find out, per object, its most recent change like this:

        select objectid, max(changedate) as LatestChange
        from LOG
        group by objectid

You can then get the color and user columns by linking the set returned above, instantiated as an inline view that has been given an alias, to the same table again:

       select color, user, FOO.objectid, FOO.LatestChange
       from LOG
       inner join
       (

          select objectid, max(changedate) as LatestChange
        from LOG
        group by objectid


        ) as FOO
        on LOG.objectid = FOO.objectid and LOG.changedate = FOO.LatestChange

How can I SELECT distinct data based on a date field?

7 Answers7