Getting the Penultimate Record from a Table Sorted by Descending ID

Question

We need to obtain the penultimate row of a table that is sorted by its ID in descending order, not by the date. Then, ensure that the table has unique date values. Finally, once the table is sorted with distinct dates, retrieve the second most recent record. How to achieve this?

create table mytable (id int, ForecastDate date);
insert into mytable values
(1,'2023-12-05'),(2,'2024-01-03'),(3,'2024-04-01'),(4,'2024-04-01'),(5,'2024-04-01');

Table:

id	ForecastDate
1	2023-12-05
2	2024-01-03
3	2024-04-01
4	2024-04-01
5	2024-04-01

from this table we need in the first step to sort out by its id. Then we need to have distinct dates. Why? The main aim is to get the second most recent record of the table, but with distinct dates.

The final result must show this record: 2024-01-03 which belongs to id 2 of mytable.

id	ForecastDate
2	2024-01-03

I have tried with these 2 CTE, but unfortunately CTE does not enable the use of ORDER BY, and I do not want to use the TOP.

WITH DistinctForecastDate AS (
    SELECT DISTINCT
          mytable.ForecastDate AS ForecastDate
    FROM
        mytable
   
   ORDER BY   mytable.id DESC
)

, RowNumberDate AS (
    SELECT DistinctForecastDate.ForecastDate AS ForecastDate
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
    FROM DistinctForecastDate
)
SELECT RowNumberDate.DeliveryDate
FROM RowNumberDate 
WHERE RowNumberDate.RowNumber = 2

I have a fiddle here https://sqlfiddle.com/sql-server/online-compiler?id=97d0be8e-635e-44f1-8d4e-c8818e24e2a5

I have tried with another query but it delivers the wrong result (2023-12-05), as you can see in the fiddle:

How to get the 2024-01-03 value? Thanks.

Answer 1

Given you have stated you don't care which Id is kept in the case of duplicate ForecastDates, then just use a simple GROUP BY to remove duplicates, then use ROW_NUMBER as you were to find the second row.

WITH cte1 AS (
    -- Remove duplicate ForecastDates 
    SELECT min(id) id, ForecastDate
    FROM mytable
    GROUP BY ForecastDate
), cte2 AS (
    -- Find the second row
    SELECT *
        , ROW_NUMBER() OVER (ORDER BY Id DESC) rn
    FROM cte1
)
-- Filter out the second row
SELECT id, ForecastDate
FROM cte2
WHERE rn = 2;

Returns:

id	ForecastDate
2	2024-01-03

DBFiddle