Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
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 VALJim 4John 5Tom 6Julie 7Rich 8Rob 9RANGE 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 VALJohn 5Tom 6Julie 7Rich 8
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-07-29 : 16:25:48
[code]SELECT name, val FROM Tbl WHERE val >= 6 AND val <= 8UNION ALLSELECT * FROM ( SELECT TOP (1) name,val FROM Tbl WHERE val < 6 ORDER BY val DESC)sORDER BY val;[/code]
bitsmed
Aged Yak Warrior
545 Posts
Posted - 2013-07-29 : 16:50:31
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;