Find Second Highest Record From Oracle Db

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. I

Solution 1:

You could use:

SELECT*FROM (SELECT*, ROW_NUMBER() OVER(PARTITIONBY id ORDERBY mia DESC) AS rn
      FROMtable) sub
WHERE rn =2;

Solution 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 :

selectmin(date_) min_date 
  from
(
 select date_
   from mytable
  where id =&i_id
  groupby id, date_
  orderby date_
  fetchfirst2rowsonly
 );