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  *
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 2

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.