sql query - pulling 2 rows [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

sql - query between 2 rows

Sorry, having a hard time modifying my previous 开发者_Python百科question. And I think I might have formed my question incorrectly...again new at this. I'm doing a select where I need to grab 2 rows. I have a value of 13000.00000 (range). I need to grab both rows 2 and 3 since it falls between the 10000 (min range) and 15000 (min range)

This statement just pulls in row 2.

select *
from TABLE1
where 13000 ?? range;


TABLE1
Row     range       return_value
1       0.00000     1.15
2       10000.00000     1.25
3       15000.00000     1.35
4       20000.00000         1.14

thanks!

---

It seems that you need to do both of the following

• Return the row that corresponds to the range that is the greatest value less than your input range

• Return the row that corresponds to the range that is the smallest value more than your input range.

  SELECT *    
  FROM TABLE1 
  WHERE Range = (select MAX(range) from table1 subt1 where subt1.range < 13000)
       OR Range = (select MIN(range) from table1 subt1 where subt1.range > 13000)
  

---

select * from table1 where range in 
(
select max(range) from (select range from table1 where range<13000)
union
select min(range) from (select range from table1 where range>13000)
)

---

Here it is in two queries:

SELECT TOP 1 * from TABLE1 where range < 13000 ORDER BY range desc;
SELECT TOP 1 * from TABLE1 where range > 13000 ORDER BY range asc;

---

Are you trying to find the closest two values to another value (i.e. the two rows that are closes to 13000)? Or are you trying to find any two rows between the range of 10000 and 15000? Was the label on the 15000 value supposed to be "max range". If so, then I am not clear on the reference to 13000.

If it is the latter then this will work.

select top 2 * from TABLE1 where range between 10000 and 15000

This will return two rows, but it doesn't make a distinction about which of the two rows that fall within the range. If you need to be more specific, Narnian's answer is a better solution.

Or you can use Paul's and combine the two select statements into a single union statement:

SELECT TOP 1 * from TABLE1 where range < 13000 ORDER BY range desc
Union
SELECT TOP 1 * from TABLE1 where range > 13000 ORDER BY range asc;