how to find the 2nd and 2nd to last entry in a table?

Question

Post reply

how to find the 2nd and 2nd to last entry in a table?

water490

Ten Centuries

Points: 1019

February 9, 2025 at 5:06 am

Go to Answer

#4533311

Hi everyone

I am working on a query where the calculation depends on whether the entry is the 2nd entry from the top or 2nd entry from the bottom. How can I find these two values?

Sample data

DROP TABLE IF EXISTS #TEST_TABLE

CREATE TABLE #TEST_TABLE
(
[UNIT] [nchar](1) NOT NULL,
[ORDER_DATE] [date] NOT NULL
)

INSERT INTO #TEST_TABLE
VALUES('A','2025-02-03'),
      ('A','2025-01-31'),
      ('A','2025-01-30'),
      ('A','2025-01-29'),
      ('A','2025-01-28'),
      ('A','2025-01-27'),
      ('A','2025-01-24'),
      ('B','2025-01-20'),
      ('B','2025-01-17'),
      ('B','2025-01-16'),
      ('B','2025-01-15'),
      ('B','2025-01-14'),
      ('B','2025-01-09')

SELECT*
FROM#TEST_TABLE
ORDER BY 1,2 DESC

Expected outcome

Unit Date1 Date2

A 2025-01-31 2025-01-27

B 2025-01-17 2025-01-14

Thank you

Phil Parkin

SSC Guru

Points: 247094

February 9, 2025 at 11:56 am

Answer

#4533464

WITH ordered
AS (SELECT UNIT
          ,ORDER_DATE
          ,rn1 = ROW_NUMBER () OVER (PARTITION BY UNIT ORDER BY ORDER_DATE)
          ,rn2 = ROW_NUMBER () OVER (PARTITION BY UNIT ORDER BY ORDER_DATE DESC)
    FROM #TEST_TABLE)
SELECT o.UNIT
      ,Date1 = MAX (IIF(o.rn2 = 2, o.ORDER_DATE, '19000101'))
      ,Date2 = MAX (IIF(o.rn1 = 2, o.ORDER_DATE, '19000101'))
FROM ordered o
WHERE o.rn1 = 2
      OR o.rn2 = 2
GROUP BY o.UNIT;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn SSC-Addicted Points: 429 More actions · Answer 1

WITH MyCTE AS (
SELECT Unit, ORDER_DATE, row_number() over (partition by unit order by ORDER_DATE desc) as RowNum
FROM #TEST_TABLE)
SELECT Unit, Order_date
FROM MyCTE
WHERE RowNum = 2;

Adi Cohn SSC-Addicted Points: 429 More actions · Answer 2

WITH MyCTE AS (
SELECT Unit, ORDER_DATE, row_number() over (partition by unit order by ORDER_DATE desc) as RowNum
FROM #TEST_TABLE)
SELECT Unit, Order_date
FROM MyCTE
WHERE RowNum = 2;

Phil Parkin SSC Guru Points: 247094 More actions · Answer 3

Adi Cohn wrote:

WITH MyCTE AS (
SELECT Unit, ORDER_DATE, row_number() over (partition by unit order by ORDER_DATE desc) as RowNum
FROM #TEST_TABLE)
SELECT Unit, Order_date
FROM MyCTE
WHERE RowNum = 2;

This does not fully match the OP's 'Expected Outcome'.

water490 Ten Centuries Points: 1019 More actions · Answer 4

Phil Parkin wrote:

WITH ordered
AS (SELECT UNIT
          ,ORDER_DATE
          ,rn1 = ROW_NUMBER () OVER (PARTITION BY UNIT ORDER BY ORDER_DATE)
          ,rn2 = ROW_NUMBER () OVER (PARTITION BY UNIT ORDER BY ORDER_DATE DESC)
    FROM #TEST_TABLE)
SELECT o.UNIT
      ,Date1 = MAX (IIF(o.rn2 = 2, o.ORDER_DATE, '19000101'))
      ,Date2 = MAX (IIF(o.rn1 = 2, o.ORDER_DATE, '19000101'))
FROM ordered o
WHERE o.rn1 = 2
      OR o.rn2 = 2
GROUP BY o.UNIT;

Thank you so much!