Find second highest record from oracle db [duplica

Question

This question already has an answer here:

• How to get second largest or third largest entry from a table 12 answers

I have the following data:

id  date    mia 

1   1/1/2017    3

1   1/2/2017    1

1   1/3/2017    2

2   1/4/2017    1

2   1/5/2017    4

2   1/6/2017    6

.
.
.
.

and so on.

If I give input as id=1 I should fetch record of 2017-02-01 and if input id=2 then record of 2017-05-01 i.e record of previous month of the highest date.

Answer 1

You could use:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY mia DESC) AS rn
      FROM table) sub
WHERE rn = 2;

Answer 2

you may not define a column named as date, instead i use date_. For former versions you may refer to @lad2025 's answer, if you're on oracle12c, you may query with the following :

select min(date_) min_date 
  from
(
 select date_
   from mytable
  where id = &i_id
  group by id, date_
  order by date_
  fetch first 2 rows only
 );