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

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

ObjID   Color   Date             开发者_StackOverflow中文版        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).

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 

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;

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

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

See these two related questions:

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

And:

How to efficiently determine changes between rows using SQL

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

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

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