SELECT ONE Row with the MAX() value on a column

Question

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.

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).

The other similar questions all appeared to be dealing with partitions of data and aggregating max value over those partitions. If someone finds this is TRULY a duplicate please mark it as such. — stevebot
– stevebot, Commented Dec 6, 2012 at 20:33

Adam Robinson · Accepted Answer · 2012-12-06 20:41:28Z

58

select top 1 * from newsletters where IsActive = 1 order by PublishDate desc

answered Dec 6, 2012 at 20:41

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BITWISE Over a year ago

This did not work for me due to the "top 1" portion. I replaced "top 1 " with "", and appended "limit 1" to fix it.

Taryn · Accepted Answer · 2012-12-06 20:42:25Z

15

You can use 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

See SQL Fiddle with Demo

You can even use a subquery that selects the max() date:

select t1.*
from table1 t1
inner join
(
  select max(publishdate) pubdate
  from table1
  where isactive = 1
) t2
  on t1.publishdate = t2.pubdate

See SQL Fiddle with Demo

answered Dec 6, 2012 at 20:42

Taryn

249k57 gold badges374 silver badges409 bronze badges

Comments

Narsimha · Accepted Answer · 2012-12-06 21:06:10Z

2

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
    )

answered Dec 6, 2012 at 21:06

Narsimha

1844 bronze badges

Collectives™ on Stack Overflow

SELECT ONE Row with the MAX() value on a column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related