SQL 如何选择最近的日期项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4090789/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL How to Select the most recent date item
提问by Matt
Hello I have a table with columns:
您好，我有一个带列的表：
*using oracle
*使用oracle
ID NUMBER
USER_ID NUMBER
DATE_ADDED DATE
DATE_VIEWED DATE
DOCUMENT_ID VARCHAR2
URL VARCHAR2
DOCUMENT_TITLE VARCHAR2
DOCUMENT_DATE DATE
I want to know how i can get the most recently added document for a given user.
我想知道如何为给定用户获取最近添加的文档。
Select * FROM test_table WHERE user_id = value AND (do something with date_added column)
Thanks
谢谢
回答by sh_kamalh
Select *
FROM test_table
WHERE user_id = value
AND date_added = (select max(date_added)
from test_table
where user_id = value)回答by gbn
Not sure of exact syntax (you use varchar2 type which means not SQL Server hence TOP) but you can use the LIMIT keyword for MySQL:
不确定确切的语法（您使用 varchar2 类型，这意味着不是 SQL Server 因此是 TOP），但您可以对 MySQL 使用 LIMIT 关键字：
Select * FROM test_table WHERE user_id = value
ORDER BY DATE_ADDED DESC LIMIT 1
Or rownum in Oracle
或者 Oracle 中的 rownum
SELECT * FROM
(Select rownum as rnum, * FROM test_table WHERE user_id = value ORDER BY DATE_ADDED DESC)
WHERE rnum = 1
If DB2, I'm not sure whether it's TOP, LIMIT or rownum...
如果是 DB2，我不确定它是 TOP、LIMIT 还是 rownum...
回答by Noel Abrahams
With SQL Server try:
使用 SQL Server 尝试：
SELECT TOP 1 *
FROM dbo.youTable
WHERE user_id = 'userid'
ORDER BY date_added desc
SELECT TOP 1 *
FROM dbo.youTable
WHERE user_id = 'userid'
ORDER BY date_added desc
回答by Vincent Savard
Assuming your RDBMS know window functions and CTE and USER_ID is the patient's id:
假设您的 RDBMS 知道窗口函数并且 CTE 和 USER_ID 是患者的 ID：
WITH TT AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY DOCUMENT_DATE DESC) AS N
FROM test_table
)
SELECT *
FROM TT
WHERE N = 1;
I assumed you wanted to sort by DOCUMENT_DATE, you can easily change that if wanted. If your RDBMS doesn't know window functions, you'll have to do a join :
我假设您想按 DOCUMENT_DATE 排序，如果需要，您可以轻松更改它。如果您的 RDBMS 不知道窗口函数，则必须执行 join ：
SELECT *
FROM test_table T1
INNER JOIN (SELECT USER_ID, MAX(DOCUMENT_DATE) AS maxDate
FROM test_table
GROUP BY USER_ID) T2
ON T1.USER_ID = T2.USER_ID
AND T1.DOCUMENT_DATE = T2.maxDate;
It would be good to tell us what your RDBMS is though. And this query selects the most recent date for every patient, you can add a condition for a given patient.
不过，最好告诉我们您的 RDBMS 是什么。此查询为每个患者选择最近的日期，您可以为给定患者添加条件。
回答by Jeffrey Kemp
You haven't specified what the query should return if more than one document is added at the same time, so this query assumes you want all of them returned:
如果同时添加多个文档，您尚未指定查询应返回的内容，因此此查询假定您希望返回所有文档：
SELECT t.ID,
t.USER_ID,
t.DATE_ADDED,
t.DATE_VIEWED,
t.DOCUMENT_ID,
t.URL,
t.DOCUMENT_TITLE,
t.DOCUMENT_DATE
FROM (
SELECT test_table.*,
RANK()
OVER (ORDER BY DOCUMENT_DATE DESC) AS the_rank
FROM test_table
WHERE user_id = value
)
WHERE the_rank = 1;
This query will only make one pass through the data.
此查询只会使数据通过一次。
回答by pavanred
Select Top 1* FROM test_table WHERE user_id = value order by Date_Added Desc
