Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select second most minimum value in Oracle

I need to write a query that selects a minimum value and it's second most minimum value from a list of integers.

Grabbing the smallest value is obvious:

select min(value) from table;

But the second smallest is not so obvious.

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

like image 408
ryebr3ad Avatar asked Nov 18 '11 19:11

ryebr3ad


People also ask

How do you find the second smallest value in SQL?

select min(price) from table where itemid not in (select itemid from table where price= (select min(price) from table));

How do I select the next minimum value in SQL?

To find the minimum value of a column, use the MIN() aggregate function; it takes as its argument the name of the column for which you want to find the minimum value. If you have not specified any other columns in the SELECT clause, the minimum will be calculated for all records in the table.

How do I select a row with minimum value in SQL?

To select data where a field has min value, you can use aggregate function min(). The syntax is as follows. SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(yourColumnName) FROM yourTableName); To understand the above syntax, let us create a table.


2 Answers

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.

like image 85
Justin Cave Avatar answered Sep 28 '22 10:09

Justin Cave


select 
  value
from
  (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'.

like image 28
GolezTrol Avatar answered Sep 28 '22 10:09

GolezTrol