Results 1 to 12 of 12
  1. #1
    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  6. #6
    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. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use BETWEEN so that you include the endpoints of the range

    but that's a very nice solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    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. #9
    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. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    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. #12
    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 07:42.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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