Choose second minimum value in Oracle

Question

I need to write a query that selects the minimum value, and the second is the minimum value from the list of integers.

Getting the smallest value is obvious:

select min(value) from table; 

But the second least is not so obvious.

For writing, this list of integers is not sequential - min can be 1000, and the second most min can be 10000.

Answer 1

Use 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 identical, except for how they handle the relationships. RANK uses a controversial style of breaking ties, so if two lines are tied to ranks 1, the next row is rank 3. DENSE_RANK gives both rows bound to the first place in rank 1, and then assigns the next row to rank 2. ROW_NUMBER randomly breaks the tie and gives one of the two rows with the lowest value, rank 1, and the second - rank 2.

Answer 2

 select value from (select value, dense_rank() over (order by value) rank from table) where rank = 2 

Advantage: you can get the third value as simple or the bottom 10 lines (rank <= 10).

Note that the performance of this query will have the correct index for the "value".

Answer 3

 SELECT MIN(value) FROM TABLE WHERE Value > (SELECT MIN(value) FROM TABLE)