CODEWITHSUNDEEP
sqlsql-servert-sql
erik7970
erik7970

Reputation: 713

Getting the latest status and date for each ID

Working on SQL Server 2008 and I've been struggling with how to query this. Suppose I have the following table:

ID      Status      Date (YYYY-MM-DD)
A-1     Open        2018-01-01
A-1     Closed      2018-01-05
B-2     Closed      2018-02-01
B-2     Open        2018-02-06
B-2     Closed      2018-02-06
C-3     Closed      2018-03-01
C-3     Open        2018-03-03
C-3     Closed      2018-03-05
C-3     Open        2018-03-07

I want the results to be a table consisting of the ID and the status and date corresponding to the most recent activity for that ID. In the event that there is both an Open and Closed status on the same date, it should default to Closed. So, using the table above, the results should be:

ID      Status      Date (YYYY-MM-DD)
A-1     Closed      2018-01-05
B-2     Closed      2018-02-06
C-3     Open        2018-03-07

Upvotes: 3

Views: 1022

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81930

There is always the WITH TIES option.

The CTE method tends to be slightly more performant.

 SELECT Top 1 with ties *
   From YourTable
   Order By Row_Number() over (Partition By ID Order By Date Desc)

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

WITH cte AS
 (
   SELECT *
     ,ROW_NUMBER()
      OVER(PARTITION BY ID  -- latest date per ID, prefer 'Closed' over 'Open'
           ORDER BY Date DESC, Status) AS rn
   FROM tab
 )
SELECT * -- all columns but rn
FROM cte
WHERE rn = 1;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269445

In SQL Server, you can use row_number(). Often the most efficient method, though, is the more old-fashioned:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.id = t.id);

This is particularly true if you have an index on t(id, date).

Another method that can have better performance is apply. This assumes that you have a table of ids:

select t.*
from ids cross apply
     (select top 1 t.*
      from t
      where t.id = ids.id
      order by t.date desc
     ) t;

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use ROW_NUMBER:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS rn
      FROM tab) sub
WHERE sub.rn = 1;

Upvotes: 1

Related Questions