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

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'

CodePudding user response：

Two last dates for an operation

select Operation, max([Date]) [Date],  min([Date]) [Previous Date]
from (
  select *, row_number() over(partition by Operation order by [Date] desc) rn
  from tbl
) t
where rn <=2
group by Operation

CodePudding user response：

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

CodePudding user response：

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