SELECCIONE UNA fila con el valor MAX () en una columna

Question

SELECCIONE UNA fila con el valor MAX () en una columna

Tengo un conjunto de datos bastante simple de boletines mensuales:

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

Y etc.

La fecha de publicación es única.

Resultado (basado en lo anterior):

id  | Name          | PublishDate   | IsActive
2   |  Newsletter 2 | 11/06/2012    |     1

Lo que quiero es bastante simple. Solo quiero el boletín 1 que isActive y PublishDate = MAX (PublishDate).

23

sql sql-server tsql

Author: stevebot, 2012-12-07

Source

3 answers

Puedes usar 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

Ver Violín SQL con Demo

Incluso puede usar una subconsulta que seleccione la fecha max():

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

Ver Violín SQL con Demo

14

Author: Taryn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-06 20:42:25

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
    )

2

Author: Narsimha,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-06 21:06:10

score 54 · Accepted Answer

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

54

Author: Adam Robinson,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-12-06 20:41:28