'Find Second maximum date from two consecutive months - PL SQL [duplicate]

I have a data table containing dates in two consecutive months and I need to find 2nd Maximum date from that using PL/SQL

Date
02-OCT-2021
30-SEP-2021
29-SEP-2021
28-SEP-2021

My Query is,

select MAX(status_date) from A where status_date not in (select MAX(status_date) from A)

29-SEP-2021 is query results but it should 30-SEP-2021

sqloracledateplsql


Solution 1:[1]

From Oracle 12, you can use:

SELECT *
FROM   table_name
ORDER BY date_column DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

Before that, you can use:

SELECT date_column
FROM   (
  SELECT date_column,
         ROW_NUMBER() OVER (ORDER BY date_column DESC) AS rn
  FROM   table_name
  ORDER BY date_column DESC
)
WHERE rn = 2;

or:

SELECT date_column
FROM   (
  SELECT date_column,
         ROWNUM AS rn
  FROM   (
    SELECT date_column
    FROM   table_name
    ORDER BY date_column DESC
  )
)
WHERE rn = 2;

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions