SQL Query for Finding a Value in Multiple Ranges

SQL query for finding a value in multiple ranges

It seems like your data model is not normalized. You should consider morjas suggestion about creating an additional table.

Below is a really ugly query that checks whether a date is in any of the three ranges, and then returns the matching rate.

select case 
        when date '2010-12-05' between range1_from and range1_to then range1_rate
        when date '2010-12-05' between range2_from and range2_to then range2_rate
        when date '2010-12-05' between range3_from and range3_to then range3_rate
       end as rate
  from events
 where date '2010-12-05' between range1_from and range1_to
    or date '2010-12-05' between range2_from and range2_to
    or date '2010-12-05' between range3_from and range3_to;

SQL : select values between multiple ranges

select id, price
from tablename
where price between 500 and 1500
   or price between 2500 and 4500

Best way to lookup a value from multiple ranges

In your example it is impossible to find the discount for a value such as 9.999... it is greater than 9.99 and less than 10 and does not fit inside any of your ranges. I would start by changing your data to:

| From | To   | Discount |
|------|------|----------|
| 0    | 10   | 0.05     |
| 10   | 20   | 0.10     |
| 20   | 50   | 0.12     |
| 50   | NULL | 0.13     |

And then you could probe the table like so:

SELECT Discount 
FROM   Discounts
WHERE  (@Value >= [From])
AND    (@Value <  [To] OR [To] IS NULL)

SQL: Checking if a number within range of multiple ranges

One option uses an exists query:

SELECT
    v.ID
FROM [Values] v
WHERE NOT EXISTS (SELECT 1 FROM [Ranges] r
                  WHERE v.[Group] = r.[Group] AND
                        v.[Value] BETWEEN r.[LowLimit] AND r.[HighLimit]);

Demo

In plain English, this will check each ID row from Values and check it there does not exist even one range which contains the value. If no such range exists, then this non matching ID would be reported.

Selecting multiple ranges in SQL BETWEEN v1 AND v2

You can try to use OR instead of AND in the multiple ranges.

UPDATE [VJuliusPrac12345]
SET [StreetViewImage] = 'temp'
WHERE [Response] = 'OK'
 AND 
 (
    [maps_ID] BETWEEN 10 AND 15 OR
    [maps_ID] BETWEEN 550 AND 570 OR
    [maps_ID] BETWEEN 1002 AND 1005
 )
GO

SQL SELECT Query for Multiple Values and Ranges in Column

I would highly recommend normalizing your data. Storing a comma-separated list of items in a single row is generally never a good idea.

Assuming you can make such a change, then something like this should work for you (although you could consider storing your ranges in different columns to make it even easier):

create table lan (allowedvan varchar(100));

insert into lan values 
  ('180'),('181'),('200'),('250-499'),('550-811'),('826-mismatched');

select *
from lan
where allowedvan = '600'
  or 
    (instr(allowedvan,'-') > 0 and 
     '600' >= left(allowedvan,instr(allowedvan,'-')-1) and
     '600' <= right(allowedvan,length(allowedvan)-instr(allowedvan,'-')) 
      )

SQL Fiddle Demo

This uses INSTR to determine if the value contains a range (a hyphen) and then uses LEFT and RIGHT to get the range. Do not use LIKE because that could return inaccurate results (600 is like 1600 for example).

If you are unable to alter your database, then perhaps look into using a split function (several posts on it on SO) and then you can do something similar to the above method.

Select multiple values from multiple range

This is a kind of pivot using ranges. The pattern is the same for a pivot query using SUM(CASE...)

SELECT
  SUM(CASE WHEN prices BETWEEN 1500 AND 1999 THEN 1 ELSE 0 END) AS `1500-1999`,
  SUM(CASE WHEN prices BETWEEN 2000 AND 2499 THEN 1 ELSE 0 END) AS `2000-2499`,
  SUM(CASE WHEN prices BETWEEN 2500 AND 2999 THEN 1 ELSE 0 END) AS `2500-2999`,
  SUM(CASE WHEN prices BETWEEN 3000 AND 3499 THEN 1 ELSE 0 END) AS `3000-3499`,
  SUM(CASE WHEN prices BETWEEN 3500 AND 3999 THEN 1 ELSE 0 END) AS `3500-3999`,
  ...
  COUNT(*) AS `All Values`
FROM
  prices

Substitute the rest of the ranges... The CASE produces a 0 or 1 if the condition is matched and those are added up by the aggregate SUM()s.

http://sqlfiddle.com/#!2/5803e/10

SQL query multiple ranges without using multiple OR clauses (nesting LIKE, BETWEEN)

Some comments deny that an IN clause can be more efficient than using OR - which i believe is incorrect for the general case.

It simply depends on the query optimizer of the database.

If the query optimizer is clever enough, it will transforms the OR and IN clause to the same execution plan (if they are semantically equal).
In this case, the further plan processing will have the same costs for both plans, one small difference might be that the costs of the transformation might incur a small difference.

On the other hand, a query optimizer might not notice the strong correlation of the OR predicates and evaluate each range predicates independently (each one being a separate operator) in an execution plan, whereas an IN clause might be handled by a lookup table of all values in the in clause (only one operator) resulting in a noticeable runtime difference.

So the overall answer is it heavily depends on your DBMS, i would suggest that you try out both versions in a small benchmark setting on your system.

For SQL-92 there is no special syntax for something like

WHERE VAR_1 IN ('[200-300]','[350-400]','[450-500]')

so indeed you have only OR's or IN lists.

Please notice that the more general notation for

WHERE VAR_1 IN ( '200', '201' )

is

WHERE VAR_1 = ANY ('200','201' )

( the operator "=" can be replaced by any other comparison operator, e.g. "<=" )

---

Related Topics