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

Answer 2

If I understand correctly you want the row with the highest ID that is part of the group of rows with the second most recent date. In SQL Server 2012 there are a couple of options to do this which do not require a CTE:

1. Using OFFSET and FETCH

    DECLARE @SECOND_LAST_DATE AS DATE;
    SET @SECOND_LAST_DATE = (
        SELECT DISTINCT(ForecastDate)
        FROM mytable
        ORDER BY ForecastDate DESC
        OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
    );
    
    SELECT TOP 1 id, ForecastDate
    FROM mytable
    WHERE ForecastDate = @SECOND_LAST_DATE
    ORDER BY id DESC;

2. Using DENSE_RANK

    SELECT TOP 1 o.id, o.ForecastDate
    FROM (
        SELECT 
            i.id, i.ForecastDate 
            , DENSE_RANK() OVER (
                ORDER BY i.ForecastDate DESC
            ) AS Rank  
        FROM mytable i
    ) o
    WHERE o.Rank = 2
    ORDER BY o.id DESC;

Answer 3

You should be able to do what you need with just one CTE, using separate window functions for ForecastDate and id.

WITH ranked AS
(
        SELECT *, 
        DENSE_RANK() OVER (ORDER BY ForecastDate DESC) AS rnk_dt,
        RANK() OVER (PARTITION BY ForecastDate ORDER BY id) AS rnk_id
    FROM mytable
)
SELECT * FROM ranked
WHERE rnk_dt = 2 AND rnk_id = 1

Of course, you can adjust the logic for the id as desired (e.g. pull the last ID instead of the first one). And we're using DENSE_RANK to handle ties as might be expected.

(fiddle here