'SQL Oracle select second max [duplicate]

I am trying to retrieve the second maximum value between some dates linked to the id. I tried this way but it keeps always giving me the highest date and not the second one:

SELECT id_dependent, max("date")
  FROM gest
 WHERE data NOT IN (SELECT MAX ("date") FROM gest)
GROUP BY id_dependent

The table is:

id date id_dependent
1. 23-apr-2022 C1
2. 15-sep-2021 C1
3. 07-jan-2019 C1
4. 12-mar-2018 C2
5. 21-nov-2020 C2
6. 05-may-2019 C2
7. 17-feb-2021 C3
8. 20-nov-2020 C3
9. 31-dec-2017 C3
10. 21-oct-2018 C4
11. 18-feb-2023 C4

Thx

sqloracle


Solution 1:[1]

Select id_dependent,dated FROM(Select id_dependent,dated ,
    RANK() over(partition by id_dependent 
    order by dated desc) rn from table )
    where rn=2

Solution 2:[2]

Try this (using analytic function)

SELECT "date"
  FROM (SELECT ROW_NUMBER() OVER 
                           (PARTITION BY id_dependent ORDER BY "date" DESC) AS row_n, 
              "date"
          FROM gest) 
 WHERE row_n = 2

Solution 3:[3]

try this if you want 2nd max by id_dependent

-- untested

select id_dependent, max(data)  from 
gest  gest_out where data not in 
      (SELECT MAX (data), id_dependent 
      FROM gest gest_in where gest_out.id_dependent = gest_in.id_dependent 
      group by id_dependent) 
       group by id_dependent

Solution 4:[4]

SELECT DATE FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID_DEPENDENT ORDER BY DATE DESC) AS NR, DATE FROM DUAL) WHERE NR= 2

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 user3369545
Solution 2 Barbaros Özhan
Solution 3 Himanshu Kandpal
Solution 4 dario59

Related Questions