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).

Answer 1

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 

Answer 2

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;