(T-SQL)首先获取所有用户及其最大日期。与表连接以查找maxdates上用户的对应值。

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

结果:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

检查这个链接，如果你的问题似乎类似的页面，然后我会建议你下面的查询，将给出该链接的解决方案

选择不同的sno,item_name,max(start_date) over(sno分区)，max(end_date) over(sno分区)，max(creation_date) over(sno分区)， Max (last_modified_date) over(sno分区) 从uniq_select_records 按sno,item_name asc排序;

是否会给出与该链接相关的准确结果

如果(UserID, Date)是唯一的，即同一个用户没有出现两次日期，则:

select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
                          from TheTable
                          group by UserID) UserMaxDate
     on TheTable.UserID = UserMaxDate.UserID
        TheTable.[Date] = UserMaxDate.MaxDate;

SELECT a.* 
FROM user a INNER JOIN (SELECT userid,Max(date) AS date12 FROM user1 GROUP BY userid) b  
ON a.date=b.date12 AND a.userid=b.userid ORDER BY a.userid;

我知道你要求使用Oracle，但是在SQL 2005中我们现在使用这个:

-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

在PostgreSQL 8.4或更高版本中，你可以这样使用:

select user_id, user_value_1, user_value_2
  from (select user_id, user_value_1, user_value_2, row_number()
          over (partition by user_id order by user_date desc) 
        from users) as r
  where r.row_number=1