SQL SELECT ONE Row 与列上的 MAX() 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13752023/
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
SELECT ONE Row with the MAX() value on a column
提问by stevebot
I have a pretty simple dataset of monthly newsletters:
我有一个非常简单的每月通讯数据集：
id | Name | PublishDate | IsActive
1 | Newsletter 1 | 10/15/2012 | 1
2 | Newsletter 2 | 11/06/2012 | 1
3 | Newsletter 3 | 12/15/2012 | 0
4 | Newsletter 4 | 1/19/2012 | 0
and etc.
等等。
The PublishDate is unique.
PublishDate 是唯一的。
Result (based on above):
结果（基于以上）：
id | Name | PublishDate | IsActive
2 | Newsletter 2 | 11/06/2012 | 1
What I want is pretty simple. I just want the 1 newsletter that IsActive and PublishDate = MAX(PublishDate).
我想要的很简单。我只想要 IsActive 和 PublishDate = MAX(PublishDate) 的 1 份时事通讯。
回答by Adam Robinson
select top 1 * from newsletters where IsActive = 1 order by PublishDate desc
回答by Taryn
You can use row_number():
您可以使用row_number()：
select id, name, publishdate, isactive
from
(
select id, name, publishdate, isactive,
row_number() over(order by publishdate desc) rn
from table1
where isactive = 1
) src
where rn = 1
You can even use a subquery that selects the max()date:
您甚至可以使用选择max()日期的子查询：
select t1.*
from table1 t1
inner join
(
select max(publishdate) pubdate
from table1
where isactive = 1
) t2
on t1.publishdate = t2.pubdate
回答by Narsimha
CREATE TABLE Tmax(Id INT,NAME VARCHAR(15),PublishedDate DATETIME,IsActive BIT)
INSERT INTO Tmax(Id,Name,PublishedDate,IsActive)
VALUES(1,'Newsletter 1','10/15/2012',1),(2,'Newsletter 2','11/06/2012',1),(3,'Newsletter 3','12/15/2012',0),(4,'Newsletter 4','1/19/2012',0)
SELECT * FROM Tmax
SELECT t.Id
,t.NAME
,t.PublishedDate
,t.IsActive
FROM Tmax AS t
WHERE PublishedDate=
(
SELECT TOP 1 MAX(PublishedDate)
FROM Tmax
WHERE IsActive=1
)
