我有一个表，它是一个关于用户何时登录的集合条目。
username, date, value
--------------------------
brad, 1/2/2010, 1.1
fred, 1/3/2010, 1.0
bob, 8/4/2009, 1.5
brad, 2/2/2010, 1.2
fred, 12/2/2009, 1.3
etc..
我如何创建一个查询，将给我每个用户的最新日期?
更新:我忘记了我需要有一个值与最近的日期。
当前回答
对于Oracle，将结果集按降序排序，并获取第一个记录，因此您将获得最新的记录:
select * from mytable
where rownum = 1
order by date desc
其他回答
对于Oracle，将结果集按降序排序，并获取第一个记录，因此您将获得最新的记录:
select * from mytable
where rownum = 1
order by date desc
这将为你编辑的问题提供正确的结果。
子查询确保只找到最近日期的行，而外部的GROUP BY将负责联系。当同一用户的同一日期有两个条目时，它将返回值最高的那个。
SELECT t.username, t.date, MAX( t.value ) value
FROM your_table t
JOIN (
SELECT username, MAX( date ) date
FROM your_table
GROUP BY username
) x ON ( x.username = t.username AND x.date = t.date )
GROUP BY t.username, t.date
你也可以使用分析秩函数
with temp as
(
select username, date, RANK() over (partition by username order by date desc) as rnk from t
)
select username, rnk from t where rnk = 1
根据我的经验，最快的方法是取表中没有新行的每一行。
另一个优点是所使用的语法非常简单，而且查询的含义相当容易掌握(取所有行，确保所考虑的用户名不存在更新的行)。
不存在
SELECT username, value
FROM t
WHERE NOT EXISTS (
SELECT *
FROM t AS witness
WHERE witness.username = t.username AND witness.date > t.date
);
ROW_NUMBER
SELECT username, value
FROM (
SELECT username, value, row_number() OVER (PARTITION BY username ORDER BY date DESC) AS rn
FROM t
) t2
WHERE rn = 1
内连接
SELECT t.username, t.value
FROM t
INNER JOIN (
SELECT username, MAX(date) AS date
FROM t
GROUP BY username
) tm ON t.username = tm.username AND t.date = tm.date;
左外连接
SELECT username, value
FROM t
LEFT OUTER JOIN t AS w ON t.username = w.username AND t.date < w.date
WHERE w.username IS NULL
SELECT t1.username, t1.date, value
FROM MyTable as t1
INNER JOIN (SELECT username, MAX(date)
FROM MyTable
GROUP BY username) as t2 ON t2.username = t1.username AND t2.date = t1.date
