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.
select min(price) from table where itemid not in (select itemid from table where price= (select min(price) from table));
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.
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.
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.
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'.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With