Penultimate Date For Each Record

I'm struggling with creation of select which shows me penultimate date for each record in my DB. For example: id date 1 01.01.2018 1 05.01.2018 1 06.02.2018 2 01.06.2018

Solution 1:

Use conditional aggregation and the ANSI-standard row_number() or dense_rank() functions:

select id,
       max(date) as max_date,
       max(casewhen seqnum =2thendateend) as penultimate_date
from (select t.*,
             dense_rank() over (partitionby id orderbydatedesc) as seqnum
      from t
     ) t
where seqnum in (1, 2)
groupby id;

Use row_number() if the dates can be the same in the event of ties.

Solution 2:

Use GROUP BY to get the MAX and a correlated subquery with another MAX but this time lower than the former.

SELECT
    T.id,
    MAX(T.date) max_date,
    (
        SELECT
            MAX(N.date)
        FROM
            YourTable N
        WHERE
            N.id = T.id AND
            N.date < MAX(T.date)
    ) penultimate
FROM
    YourTable T
GROUPBY
    T.id

Solution 3:

Just an opitimized query:

;WITH cte AS
(
    SELECT id AS ID
        ,[date] AS max_date
        ,LEAD ([date], 1, 0) OVER (PARTITION BY id ORDERBY [date] DESC) AS penultimate
        ,ROW_NUMBER() OVER(PARTITION BY id ORDERBY [date] DESC) AS RN
    FROM Table3
)
SELECT ID,max_date,penultimate
FROM cte
WHERE RN=1

SQL Fiddle

Solution 4:

I wrote in this way,

SELECT ID
    ,max(StartDate) MaxDate
    ,(
        SELECT StartDate
        FROM YourTable t2
        WHERE t2.id = t1.id
        ORDERBY StartDate DESCOFFSET1ROWSFETCH NEXT 1ROWONLY
        ) penultimate
FROM YourTable t1
GROUPBY id