CODEWITHSUNDEEP
sqloracle-databaseoracle11ggreatest-n-per-group
ryebr3ad
ryebr3ad

Reputation: 1248

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.

Upvotes: 8

Views: 15487

Answers (4)

Appikonda sai
Appikonda sai

Reputation: 1

select min(fine_amount) as second_min_fine_amount
from bill
where fine_amount not in (select min(fine_amount) from bill);

Upvotes: -1

JNK
JNK

Reputation: 65147

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

Upvotes: 7

GolezTrol
GolezTrol

Reputation: 116100

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

Upvotes: 7

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 17

Related Questions