Choose values ​​between two ranges of columns

Question

I have a table like this:

i1 i2 ---------- 1 a 1 b 1 c 1 d 2 x 3 y 4 a 4 b 4 c 

I want to select lines between 1 c and 4 a.
The result should be:

 1 c 1 d 2 x 3 y 4 a 

How can i do this?

Answer 1

I would do it like:

 select t.* from t where (i1 > 1 or (i1 = 1 and i2 >= 'c')) and (i1 < 4 or (i1 = 4 and i2 <= 'a')); 

Answer 2

If you are using a database that supports row number functionality, one of them is to create a CTE of your table with row numbers in the order you specify (i.e., in ascending order on i1 , and then on i2 second).

Then use two subqueries to identify line numbers for 1c and 4a . These line numbers make up the range you want to select.

 ;WITH cte AS ( SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS RowNumber, i1, i2 FROM yourTable ) SELECT * FROM cte t WHERE t.RowNumber >= (SELECT RowNumber FROM cte WHERE i1=1 AND i2='c') AND t.RowNumber <= (SELECT RowNumber FROM cte WHERE i1=4 AND i2='a') 

Answer 3

Of course, this is not an optimal solution, but this query should work:

 select i1, i2 from tbl where (i1 > 1 and i1 < 4) or (i1 = 1 and i2 >='c') or (i1 = 4 and i2 <='a') 

Note that (1, c) and (4, a) are included in the results. Change the comparison operators if you do not need to include borders.

Answer 4

Not such a beautiful way to do it ... but

 create procedure GetRangeBetween (@i11 int, @i12 char, @i21 int, @i22 char) AS BEGIN if object_id ('tempdb..#Test') is not null drop table #Test create table #Test (i1 int, i2 nvarchar(10), [Rank] int) insert into #Test(i1, i2) values (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'x'), (3, 'y'), (4, 'a'), (4, 'b'), (4, 'c') update #Test set Rank = src.[srcRank] from #Test t join (select *, row_number() over (order by i1) [srcRank] from #Test) src on t.i1 = src.i1 and t.i2 = src.i2 declare @Rank1 int = (select [Rank] from #Test where i1 = @i11 and i2 = @i12) declare @Rank2 int = (select [Rank] from #Test where i1 = @i21 and i2 = @i22) select i1, i2 from #Test where (i1 between @i11 and @i21) and ([Rank] between @Rank1 and @Rank2) END 

Then you just execute it with ...

 execute GetRangeBetween 1, 'c', 4, 'a'