SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT where value in a range and below a range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cheinzle
Starting Member

USA
1 Posts

Posted - 07/29/2013 :  16:17:48  Show Profile  Reply with Quote
I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:

NAME VAL
Jim 4
John 5
Tom 6
Julie 7
Rich 8
Rob 9

RANGE TO SELECT is 6-8 (inclusive)

Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)

NAME VAL
John 5
Tom 6
Julie 7
Rich 8

James K
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 07/29/2013 :  16:25:48  Show Profile  Reply with Quote
SELECT name, val FROM Tbl WHERE val >= 6 AND val <= 8
UNION ALL
SELECT * FROM 
(
	SELECT TOP (1) name,val FROM Tbl WHERE val < 6 ORDER BY val DESC
)s
ORDER BY val;
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

397 Posts

Posted - 07/29/2013 :  16:50:31  Show Profile  Reply with Quote
Alternative:

declare @yourtable table (name varchar(10),val int);
declare @fromval int;
declare @toval int;

set @fromval=6;
set @toval=8;

insert into @yourtable
 values ('Jim',4)
       ,('John',5)
       ,('Tom',6)
       ,('Julie',7)
       ,('Rich',8)
       ,('Rob',9)
;

select a.*
  from @yourtable as a
       inner join (select max(val) as val
                     from @yourtable
                    where val<@fromval
                  ) as b
               on b.val<=a.val
 where a.val<=@toval
 order by a.val
;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000