# Thread: Help On query to select range of rows identified by composite key

1. Registered User
Join Date
Jul 2006
Posts
5

## Unanswered: Help On query to select range of rows identified by composite key

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)

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
what happens if you had a row with (c1=1,c2=3,c3=1)?

include or not?

if so, why? if not, why?

3. Registered User
Join Date
Jul 2006
Posts
5
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.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
please test this and let me know what you get --
Code:
```select c1
, c2
, c3
from T1
where c1 = 1
and c2 = 2
and c3 >= 1
or c1 = 1
and c2 > 2
or c1 = 2
and c2 = 1
and c3 <= 2
or c1 = 2
and c2 < 1```

5. Registered User
Join Date
Jul 2006
Posts
5
Thanks . It works for the above set of data.

6. Registered User
Join Date
Jul 2002
Posts
48
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

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
use BETWEEN so that you include the endpoints of the range

but that's a very nice solution

8. Registered User
Join Date
Jul 2006
Posts
5
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

9. Registered User
Join Date
Dec 2005
Posts
273
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

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by UmaMaheshwari
And the query of r937 will not work if the range is as follows:
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

11. Registered User
Join Date
Jul 2006
Posts
5
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!

12. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
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:
Code:
```SELECT * FROM T1
WHERE c1 BETWEEN s1 AND e1
AND ((c1 > s1 AND c1 < e1) OR
(c1 < e1 AND (c2 > s2 OR c2 = s2 AND c3 >= s3)) OR
(c1 > s1 AND (c2 < e2 OR c2 = e2 AND c3 <= e3))
)```
Last edited by Peter.Vanroose; 07-31-06 at 06:42.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•