SQL. Is there any efficient way to find second lowest value?

Question

I have the following table:

 ItemID Price
    1   10
    2   20
    3   12
    4   10
    5   11

I need to find the second lowest price. So far, I have a query that works, but i am not sure it is the most efficient query:

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

What if I have to find third OR fourth minimum price? I am not even mentioning other attributes and conditions... Is there any more efficient way to do this?

PS: note that minimum is not a unique value. For example, items 1 and 4 are both minimums. Simple ordering won't do.

score 11 · Answer 1 · edited Nov 19 '18 at 22:31

11

SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
                FROM table )

edited Nov 19 '18 at 22:31

Nick

138,499
22
57
95

answered Nov 08 '13 at 07:49

Hashid Hameed

878
1
8
23

Tom Studee · Accepted Answer · 2013-06-08T01:28:40.200

4

select price from table where price in (
    select 
        distinct price 
    from 
    (select t.price,rownumber() over () as rownum from table t) as x 
    where x.rownum = 2 --or 3, 4, 5, etc
)

edited Jun 08 '13 at 01:28

answered Jun 08 '13 at 00:52

Tom Studee

10,316
4
38
42

it might work, but the it is not necessarily row 2. there could be two minimum values as above ItemID=1 and 4 both have 10 – Buras Jun 08 '13 at 01:11
@Buras how bout the above? my apologies if it doesn't work, I don't have a db2 instance to test it out on. – Tom Studee Jun 08 '13 at 01:29
@TomStudee I believe the function is spelled row_number(). – WarrenT Jun 08 '13 at 04:10
1

@Buras using row_number() on an intermediate result set of distinct values, there can be no duplicate values on the innermost SELECT. The outermost SELECT seems superfluous as written as it shows duplicate prices but nothing with it. However, in other examples you might consider using dense_rank() since it will identify multiple rows if they are tied for second place. The rank() function is slightly different and probably not useful here, because if there are tied values, then rank numbers will be skipped. In a tie for 1st place, 2nd place will be skipped by rank(), but not by dense_rank(). – WarrenT Jun 08 '13 at 04:31

user1845791 · Answer 3 · 2013-06-08T01:27:42.550

4

Not sure if this would be the fastest, but it would make it easier to select the second, third, etc... Just change the TOP value.

UPDATED

SELECT MIN(price)
FROM table
WHERE price NOT IN (SELECT DISTINCT TOP 1 price FROM table ORDER BY price)

edited Jun 08 '13 at 01:27

answered Jun 08 '13 at 01:00

user1845791

993
2
9
17

it is a good solution, but there might be two minimum values, and would not work – Buras Jun 08 '13 at 01:09
TOP is not available in DB2 – WarrenT Jun 08 '13 at 04:32

score 2 · Answer 4 · edited Oct 14 '21 at 14:59

2

To find out second minimum salary of an employee, you can use following:

select min(salary) 
from table 
where salary > (select min(salary) from table);

edited Oct 14 '21 at 14:59

Stephen Ostermiller

23,933
14
88
109

answered Oct 14 '21 at 14:19

user17151126

21
1

score 1 · Answer 5 · answered Nov 14 '19 at 13:48

This is a good answer:

SELECT MIN( price )
FROM table
WHERE price > ( SELECT MIN( price )
                FROM table )

Make sure when you do this that there is only 1 row in the subquery! (the part in brackets at the end).

For example if you want to use GROUP BY you will have to define even further using:

SELECT MIN( price )
FROM table te1
WHERE price > ( SELECT MIN( price )
                FROM table te2 WHERE te1.brand = te2.brand)
GROUP BY brand

Because GROUP BY will give you multiple rows, otherwise you will get the error:

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

score 0 · Answer 6 · answered Aug 03 '18 at 11:43

I guess a simplest way to do is using offset-fetch filter from standard sql, distinct is not necessary if you don't have repeat values in your column.

select distinct(price) from table order by price offset 1 row fetch first 1 row only;

no need to write complex subqueries....

In amazon redshift use limit-fetch instead for ex...

Select distinct(price) from table
order by price
limit 1
offset 1;

score 0 · Answer 7 · edited Jan 17 '19 at 16:29

0

You can either use one of the following:-

select min(your_field) from your_table where your_field NOT IN (select distinct TOP 1 your_field from your_table ORDER BY your_field DESC)

OR

select top 1 ColumnName from TableName where ColumnName not in (select top 1 ColumnName from TableName order by ColumnName asc)

edited Jan 17 '19 at 16:29

Suraj Rao

29,388
11
94
103

answered Jan 17 '19 at 16:26

Deepak Sunilkumar

11
1

score 0 · Answer 8 · answered Jul 15 '19 at 15:48

I think you can find the second minimum using LIMIT and ORDER BY

select max(price) as minimum from (select distinct(price) from tableName order by price asc limit 2 ) --or 3, 4, 5, etc

if you want to find third or fourth minimum and so on... you can find out by changing minimum number in limit. you can find using this statement.

score 0 · Answer 9 · answered Oct 14 '21 at 14:52

You can use RANK functions, it may seem complex query but similar results like other answers can be achieved with the same,

WITH Temp_table AS (SELECT ITEM_ID,PRICE,RANK() OVER (ORDER BY PRICE) AS 
Rnk 
FROM YOUR_TABLE_NAME)
SELECT ITEM_ID FROM Temp_table
WHERE Rnk=2;

score 0 · Answer 10 · answered Jul 23 '22 at 19:04

Maybe u can check the min value first and then place a not or greater than the operator. This will eliminate the usage of a subquery but will require a two-step process

select min(price)
from table
where min(price) <> -- "the min price you previously got"

SQL. Is there any efficient way to find second lowest value?

10 Answers10