Geek Answers Handbook

Finding the smallest value in a table that exceeds a specific value

Say I have the following data

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

Imagine that they are volumes of boxes. I have some items that I want to put in boxes, and I want the smallest box. I need a SQL query that will:

  • Return a row with the smallest row larger than my query parameter
  • If there is no such line, return the largest line.

It is easy to split this into 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 something in one request, if possible, to eliminate overhead (both code switching and context switching), and it looks like it should be possible. This is probably very obvious, but the sun shines on me all day, and I can’t think!

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

I am on Oracle 11g, so any special Oracle friendliness is fine.

+4
source share
6 answers
 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 effectively use the index on mytable(value) and COUNT(STOPKEY) .

See this blog post for performance details:

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

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

In any case, this query subquery will be fast with the correct index in the Value column.

In all seriousness, you really have to do this in two queries (or two steps in one stored procedure if you want to save them in one place), because the second query is not needed if the first query is running. Combining them into one request necessarily gives you an unconditional second (or sub) request. You have to query the table twice, so the question is whether you query it twice always or only if necessary.

+1
source

Just for fun, I made the assumption that the target sizes come from the package table and you want to find boxes for the package package. COALESCE selects 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 one operator to compare with other solutions, use

 SELECT COALESCE(MIN(b1.size), MAX(b2.size) AS boxsize FROM Table AS t1, Table AS t2 WHERE targetsize < t1.Value
+1
source 
 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)
+1
source

It works. Replace "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)

A 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
0
source 
 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
-1
source

More articles:


All Articles