Select second most minimum value in Oracle
Use an analytic function
SELECT value FROM (SELECT value, dense_rank() over (order by value asc) rnk FROM table) WHERE rnk = 2
The analytic functions RANK
, DENSE_RANK
, and ROW_NUMBER
are identical except for how they handle ties. RANK
uses a sports-style process of breaking ties so if two rows tie for a rank of 1, the next row has a rank of 3. DENSE_RANK
gives both of the rows tied for first place a rank of 1 and then assigns the next row a rank of 2. ROW_NUMBER
arbitrarily breaks the tie and gives one of the two rows with the lowest value a rank of 1 and the other a rank of 2.
select valuefrom (select value, dense_rank() over (order by value) rank from table)where rank = 2
Advantage: You can get the third value just as easy, or the bottom 10 rows (rank <= 10).
Note that the performance of this query will benefit from a proper index on 'value'.
SELECT MIN(value)FROM TABLEWHERE Value > (SELECT MIN(value) FROM TABLE)