Finding the lowest value in a table greater than a certain value

Question

Say I have the following data

Name      Value
===============
Small        10
Medium      100
Large      1000

Imagine that these represent the volumes of boxes. I have some items that I want to put in the boxes, and I want the smallest box possible. I need an SQL query that will:

1. Return the row with the smallest row greater than my query parameter
2. If there is no such row, then return the largest row.

It is easy to split this up in to two queries (i.e. query point 1 first and if no rows are returned, select the largest number from the table). However, I like to do things in one query if possible to eliminate overhead (both code and context switching), and it looks like it should be possible to do. It's probably very obvious, but the Sun has been shining on me all day and I can't think!

So for example, I want the query to return 10 if you use a parameter of 5, 100 if you use a parameter of 15 and 1000 if you use anything greater than 100 (including numbers greater than 1000).

I'm on Oracle 11g, so any special Oracle goodness is OK.

Answer 1

SELECT MAX(Value)
FROM Table
WHERE Value <= LEAST(@param,(SELECT MAX(Value) FROM Table))

I'm not that familiar with Oracle but I'm sure it has a LEAST function or something equivalent.

In any case, this query's subquery will be swift with the right index on the Value column.

In all seriousness you really should do this in two queries (or two steps in one stored procedure if you want to keep them in the same place), because the second query is unnecessary if the first query works. Combining them in one query necessarily gives you an unconditional second (or sub-) query. You have to query the table twice, so the question is whether you query it twice always or just when necessary.

Answer 2

select a.newvalue from (
select MIN(value) as newvalue, 1 as order  From table where value > @param
union select MAX(value) as newvalue, 2 as order from table) A
order by a.order

Answer 3

SELECT  *
FROM    (
        SELECT  *
        FROM    (
                SELECT  *
                FROM    mytable
                WHERE   value > 10000
                ORDER BY
                        value
                )
        UNION ALL
        SELECT  *
        FROM    (
                SELECT  *
                FROM    mytable
                ORDER BY
                        value DESC
                )
        )
WHERE   rownum = 1

This will both efficiently use an index on mytable(value) and COUNT(STOPKEY).

See this article in my blog for performance details:

• Selecting lowest value

Answer 4

Just for fun I made the assumption that the target sizes are coming from a table of packages and you want to find the boxes for a bunch of packages. COALESCE chooses the second value if the first is NULL.

SELECT  
    p.pkgid,  
    p.pkgsize,  
    COALESCE(MIN(b1.size), MAX(b2.size) AS boxsize    
FROM packages AS p  
LEFT JOIN boxes AS b1 ON p.pkgsize < b1.boxsize  
LEFT JOIN boxes AS b2  -- yes, a cartesian join 
GROUP BY p.pkgid, p.pkgsize

As a single statement to compare to the other solutions, use

SELECT  
    COALESCE(MIN(b1.size), MAX(b2.size) AS boxsize    
FROM Table AS t1,  
     Table AS t2   
WHERE targetsize < t1.Value

Answer 5

This works. Replace the "5" with your parameter.

select min(basket_value) as basket_value
from baskets
where basket_value > 5 
   or basket_value = (select max(basket_value) from baskets)

Simple script to generate test data:

create table baskets(
  basket_name varchar2(20)
 ,basket_value number
);

insert into baskets(basket_name,basket_value) values('Small',10);
insert into baskets(basket_name,basket_value) values('Medium',100);
insert into baskets(basket_name,basket_value) values('Large',1000);
commit;

--drop table baskets;  --run when finished

Answer 6

WITH ranges_table AS
     (SELECT     LEVEL * 100 AS range_value
            FROM DUAL
      CONNECT BY LEVEL <= 20)
SELECT MIN (range_value)
  FROM ranges_table
 WHERE range_value >= 5 OR range_value = (SELECT MAX (range_value)
                                            FROM ranges_table)