hi,

can anybody help me with the query for the following case.
Say, a table called T1 with columns c1,c2,c3 as primary key ( composite key )
I have to select range of rows from the above table between values -
start values for c1 ,c2,c3 is s1,s2,s3
end values for c1,c2,c3 is e1,e2,e3 and ordered by c1,c2,c3.
sample rows:
1 1 1
1 1 2
1 2 1
1 2 2
2 1 1
2 1 2
2 2 2

the query must return all rows between (c1=1,c2=2,c3=1) and (c1=2,c2=1,c3=2)

what happens if you had a row with (c1=1,c2=3,c3=1)?

include or not?

if so, why? if not, why?

The row 1 3 1 will be included bcoz it does fall in the range

1 2 1 and 2 1 2 .

It falls within this range bcoz while listing , order by is used
order by c1,c2,c3.

please test this and let me know what you get --

Thanks . It works for the above set of data.

I think this query might also work. Check it out
select * from t3 where (t1*100+t2*10+t3*1) > 121 and (t1*100+t2*10+t3*1)<212

use BETWEEN so that you include the endpoints of the range

but that's a very nice solution

The solution is a good one but it restricts my columns to ne of type integer.
I have to write a generic query for columns which can be of type string , alphanumeric etc.
And the query of r937 will not work if the range is as follows:

1 1 1
1 2 1
2 1 1
2 2 3
3 1 1
3 1 2

if the range is between 1 2 1 and 312 , it does not pick the rows with c1=2.
hence i extended the solution given by r937 as


select c1
, c2
, c3
from t1
where c1 = 1
and c2 = 2
and c3 >= 1
or c1 = 1
and c2 > 2
or c1 > 1
intersect
select c1
, c2
, c3
from t1
where c1=3 and c2 = 2 and c3 < 2 or c1=3 and c2 <2 or c1 < 3

consider concatinating the columns ( if they are fixed-length CHARs ):

WHERE C1 CONCAT C2 CONCAT C3
BETWEEN C1_min CONCAT C2_min CONCAT C3_min
AND C1_max CONCAT C2_max CONCAT C3_max

of course not!!!

you cannot give a silly series and ask for a query, then after you got the query, change the silly series and then claim that the query won't work

of course not!!!

now, i could modify my query for the new silly series, but i'm not going to

good luck to you, sir

I am not sure why you got offended . The series is not changed . I only gave a sample set of data for three cols ordered by co1 , col2 , col3. I tested your query and posted a set of sample data for which the query did not bring the results and I posted the results bcoz I thought it might help someone and also I can correct myself it someone has a better answer.


I would also request you to refrain from answering the queries that you consider "silly" . Let it be answered by someone for whom it makes sense.

Thank you anyway!

There are several ways to do this, which may differ in performance depending on the actual table data and presence of indexes etc.
The following should work, I believe: