sql to find inbetween values

Question

I have a table like this

 A       B
-----------
 1.2     2.3
 2.3     3.9
 3.9     4.34
 4.34    5.786

what the data means is that 1.2-5.786 is the length of some table and 1.2-2.3 has some attributes 2.3-3.9 has some attributes and so on... but 1.2 - 5.786 when you consider it is the length of entire table(for example here).

I want to run a query which would give me rows between two values: for example:

A =1.1 amd B = 4.234

I am having hard time to figure out how to write a query, most of the queries I write leave out some edge cases.

I am trying to get all the rows in between those values like when a =1.3 and b = 4.234 i want the first three rows. since a = 1.3 is between 1.2 and 2.3 of the first row and b = 4.234 is between 3.9 and 4.34 in the third row. so i want the first three rows in my result set. — oortcloud_domicile, Mar 26 '13 at 23:06
Given the range (1.1, 4.234), you want to find all values in the table where the range overlaps the given range. The primary tricky part is 'branching the right way on equality', but you have an answer that works. See also: [Determine whether two date ranges overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/); the logic applies to any ranges, not just date ranges. — Jonathan Leffler, Mar 26 '13 at 23:52

score 1 · Accepted Answer · answered Mar 26 '13 at 23:28

This seems to work well:

select *
from yourtable
where (b > 1.1 and a <= 4.234)

SQL Fiddle Demo

Basically make sure b is greater than the lower bound and a is less than or equal to the upper bound. Depending on your desired results, you may need to change b > to b >=.

score 0 · Answer 2 · answered Mar 26 '13 at 23:04

0

For example, if you want the values between A =1.1 amd B = 4.234 then the output is

  A       B

1.2         2.3

2.3         3.9

then the query is:

SELECT * from my_table WHERE A>=1.1 AND B<=4.234

But if you want the output

  A       B

1.2         2.3

2.3         3.9

3.9         4.34

Then you want the query

SELECT * FROM my_table WHERE A >= 1.1 AND A < 4.234 B > 4.234

answered Mar 26 '13 at 23:04

000

26,951
10
71
101

what if a= 1.3 then A>=1.3 will skip the first row but 1.3 is between 1.2 and 2.3 which is te first row. – oortcloud_domicile Mar 26 '13 at 23:28
Then your question is badly defined. – 000 Mar 26 '13 at 23:30

WarrenT · Answer 3 · 2013-03-26T23:40:22.763

0

It sounds like you want

SELECT a, b
  FROM my_table 
  WHERE A >= 1.1 AND B <= 4.234
     OR A >= 1.1 AND A <  4.234 
     OR B >  1.1 AND B <= 4.234

This assumes A is always < B

edited Mar 26 '13 at 23:40

answered Mar 26 '13 at 23:32

WarrenT

4,502
19
27

sql to find inbetween values

3 Answers3