Get Records having the 2nd highest value

SQL to fetch all records having the 2^nd highest value in a given column. This is for Oracle(pl-sql) dialect

SELECT *

FROM some_table

WHERE val=

(SELECT MAX(val)

FROM some_table

WHERE val <>

(SELECT MAX(val) FROM some_table

)

);

If you need only the 2nd largest value, then you can use

SELECT MAX(val)

FROM some_table

WHERE val <>

(SELECT MAX(val) FROM some_table

)

Similar statement holds good for 2nd smallest as well. Use MIN instead of MAX.
.