SQL Query combine 2 rows to one but add column with 2nd latest date

Question

I have a SQL database that records operations in a factory. I am trying to get a TOP 1 Order by Date Desc query to also include the previous Date all on one row. I cant for the life of my think how to do this. I have tried a couple of case when statements but I cant seem to get it all on one row. Can anyone help me please?

Current Result

Operation	Date
AA	2022-08-15 12:42:34.703
AA	2022-08-15 12:37:52.167

Required Result

Operation	Date	Previous Date
AA	2022-08-15 12:42:34.703	2022-08-15 12:37:52.167

Required Result

'ID Date Previous End Date

AA 2022-08-15 12:42:34.703 2022-08-15 12:37:52.167'

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — Igor, Aug 15 '22 at 13:26
Please add a [tag](https://stackoverflow.com/help/tagging) (not a comment) for the database product you are using. — Igor, Aug 15 '22 at 13:31
Are you looking for the first and last dates of all data with an Id of AA or just each record and it's predecessor. Will there be gaps that need to be accounted for. The solution to the query above is straightforward, however, I doubt that is a complete dataset. — Ross Bush, Aug 15 '22 at 13:31
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Igor, Aug 15 '22 at 13:32
Hi Ross its a complete dataset. There is no gaps just a forever long list of dates and I would like to see the latest date and its predecessor on one row. — Adam Bushell, Aug 15 '22 at 13:35

DannySlor · Answer 1 · 2022-08-15T18:25:02.847

If you need is the last of all events:

Here's a simple solution.
Please note that with this solution you can easily select additional columns of the last record.

select top 1 
       
       Operation 
      ,Date
      ,lag(date, 1) over (partition by Operation order by date) as previous_date
from t
order by date desc

Operation	Date	previous_date
AA	2022-08-15 12:42:34.703	2022-08-15 12:37:52.167

Fiddle

If you need the last event per operation:

Please note the use of lead with descending order instead of lag in ascending order, in order to match the order of the row_number function.

select  Operation
       ,Date
       ,previous_date

from   (select Operation 
              ,Date
              ,lead(date, 1) over (partition by Operation order by date desc) as previous_date
              ,row_number()  over (partition by Operation order by date desc) as rn
        from t
        ) t
where   rn = 1

Operation	Date	previous_date
AA	2022-08-15 12:42:34.703	2022-08-15 12:37:52.167

Fiddle

score 0 · Answer 2 · answered Aug 15 '22 at 13:51

Also in the case of absence of previous date value (if you need to get NULL for Previous Date column) you can use a query like this

WITH op_rn AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY operation ORDER BY date DESC) AS rn
    FROM operations        
)
SELECT DISTINCT 
     op_rn.operation AS operation,
     op_rn.date,
     op_rn2.date AS prev_date
FROM op_rn
LEFT JOIN op_rn op_rn2 ON op_rn.operation = op_rn2.operation AND op_rn2.rn = 2
WHERE op_rn.rn = 1

or use subqueries like this

SELECT
    o.operation,
    o.max_date AS date,
    (SELECT 
         MAX(o2.date) 
     FROM operations o2 
     WHERE o2.operation = o.operation AND o2.date < o.max_date) AS prev_date
FROM (
    SELECT 
       operation,
       MAX(date) AS max_date
    FROM operations   
    GROUP BY operation
) o

score -1 · Answer 3 · answered Aug 15 '22 at 13:52

You could use the lead() window function to grab that following value

with t1 as (
    select id
        , date
        , lead(date, 1) over (partition by id order by date desc) as previous_date
        , row_number() over (partition by id order by date desc) as row_num
    from t1)
select id, date, previous_date
from t1
where row_num=1

If you're using snowflake you can use the qualify() function to filter window functions, and then you wouldn't have to filter out the row using a CTE

select id
    , date
    , lead(date, 1) over (partition by id order by date desc) as previous_date
from t1
qualify row_number() over (partition by id order by date desc) = 1

SQL Query combine 2 rows to one but add column with 2nd latest date

'ID Date Previous End Date

3 Answers3