SQL 如何根据最大日期获取不同的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23336663/
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
how to get the distinct records based on maximum date?
提问by user2514925
I'm working with Sql server 2008.i have a table contains following columns,
我正在使用 Sql server 2008.i 有一个包含以下列的表，
Id,
Name,
Date
this table contains more than one record for same id.i want to get distinct id having maximum date.how can i write sql query for this?
此表包含多个相同 ID 的记录。我想获得具有最大日期的不同 ID。我该如何为此编写 sql 查询？
回答by jeroenh
Use the ROW_NUMBER() functionand PARTITION BY clause. Something like this:
使用 ROW_NUMBER () 函数和 PARTITION BY 子句。像这样的东西：
SELECT Id, Name, Date FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date desc) AS ROWNUM
FROM [MyTable]
) x WHERE ROWNUM = 1
回答by sallushan
If you need only IDcolumn and other columns are NOT required, then you don't need to go with ROW_NUMBERor MAXor anything else. You just do a Group Byover IDcolumn, because whatever the maximum date is you will get same ID.
如果您只需要ID列而不需要其他列，那么您不需要使用ROW_NUMBER或MAX或其他任何东西。您只需做一个Group By过ID列，因为无论最大日期是什么，您都将获得相同的 ID。
SELECT ID FROM table GROUP BY ID
--OR
SELECT DISTINCT ID FROM table
If you need IDand Datecolumns with maximum date, then simply do a Group Byon IDcolumn and select the MaxDate.
如果您需要ID和Date最大日期列，然后简单地做一个Group By上ID栏，然后选择Max日期。
SELECT ID, Max(Date) AS Date
FROM table
GROUP BY ID
If you need all the columns but 1 line having Max. date then you can go with ROW_NUMBERor MAXas mentioned in other answers.
如果您需要除 1 行之外的所有列都具有 Max. 日期然后你可以去ROW_NUMBER或MAX如其他答案中提到的那样。
SELECT *
FROM table AS M
WHERE Exists(
SELECT 1
FROM table
WHERE ID = M.ID
HAVING M.Date = Max(Date)
)
回答by Nins Gosai
select Max(Date) as "Max Date" from table group by Id order by Id
从表组中选择 Max(Date) 作为“Max Date” by Id order by Id
回答by Tim Schmelter
One way, using ROW_NUMBER:
一种方法，使用ROW_NUMBER：
With CTE As
(
SELECT Id, Name, Date, Rn = Row_Number() Over (Partition By Id
Order By Date DESC)
FROM dbo.TableName
)
SELECT Id --, Name, Date
FROM CTE
WHERE Rn = 1
If multiple max-dates are possible and you want all you could use DENSE_RANKinstead.
如果可能有多个最大日期并且您想要所有可以使用的日期DENSE_RANK。
Here's an overview of sql-server's ranking function: http://technet.microsoft.com/en-us/library/ms189798.aspx
这里是sql-server的排名功能的概述：http: //technet.microsoft.com/en-us/library/ms189798.aspx
By the way, CTEis a common-table-expressionwhich is similar to a named sub-query. I'm using it to be able to filter by the row_number. This approach allows to select all columns if you want.
顺便说一下，CTE是一个类似于命名子查询的公共表表达式。我正在使用它来过滤row_number. 如果需要，此方法允许选择所有列。
回答by Milen
Try with Max(Date)and GROUP BYthe other two columns (the ones with repeating data)..
尝试使用Max(Date)和GROUP BY其他两列（具有重复数据的列）。
SELECT ID, Max(Date) as date, Name
FROM YourTable
GROUP BY ID, Name
回答by Singaravelan
You may try with this
你可以试试这个
DECLARE @T TABLE(ID INT, NAME VARCHAR(50),DATE DATETIME)
INSERT INTO @T VALUES(1,'A','2014-04-20'),(1,'A','2014-04-28')
,(2,'A2','2014-04-22'),(2,'A2','2014-04-24')
,(3,'A3','2014-04-20'),(3,'A3','2014-04-28')
,(4,'A4','2014-04-28'),(4,'A4','2014-04-28')
,(5,'A5','2014-04-28'),(5,'A5','2014-04-28')
SELECT T.ID FROM @T T
WHERE T.DATE=(SELECT MAX(A.DATE)
FROM @T A
WHERE A.ID=T.ID
GROUP BY A.ID )
GROUP BY T.ID
回答by T. Monomach
select id, max(date) from NameOfYourTable group by id;
