Need to find Second maximum date from two consecutive months

I have a data table containing dates 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

CodePudding user response：

With a little help of analytic function (sample data in lines #1 - 6; temp CTE "calculates" rank of each status_date; final query (line #12 onward) returns the result):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with a (status_date) as
  2    (select date '2021-10-02' from dual union all
  3     select date '2021-09-30' from dual union all
  4     select date '2021-09-29' from dual union all
  5     select date '2021-09-28' from dual
  6    ),
  7  temp as
  8    (select status_date,
  9            rank() over (order by status_date desc) rnk
 10     from a
 11    )
 12  select status_date
 13  from temp
 14  where rnk = 2;

STATUS_DAT
----------
30.09.2021

SQL>

CodePudding user response：

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