如何根据日期字段选择不同的数据？

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 

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

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;

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

OUTER APPLY

O.ObjID

H.ObjID

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

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

       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

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