Take the 2-minute tour ×

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.

I appreciate all help

share|improve this question

3 Answers 3

up vote 14 down vote accepted

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.

share|improve this answer
+1 Same solution as I posted, but with a good additional explanation of the difference between the three similar analytical function. –  GolezTrol Nov 18 '11 at 19:43
Analytic functions! Why didn't I think of them before. I appreciate the help and the explanation. –  ryebr3ad Nov 18 '11 at 19:59
    dense_rank() over (order by value) rank
  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'.

share|improve this answer
share|improve this answer
Note that this requires hitting the table twice so it will be more expensive than the analytic function solution. As written, it is also hard to extend when you want the 3rd or the 5th or the 10th row. You could do a COUNT(*) of the number of rows in table that have a smaller value than the current row from the outer query which is easier to extend but much less efficient. –  Justin Cave Nov 18 '11 at 19:42
@JustinCave - all valid points –  JNK Nov 18 '11 at 19:44
I upvoted your solution because it is clever, but I would not use that. –  Benoit Nov 21 '11 at 10:57

Your Answer


