Results 1 to 10 of 10

Thread: Pair of records

  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy Unanswered: Pair of records

    Hi folks,

    I am trying to write a query to get data in pairs, for example, i have data like this:
    sr_no week_no
    1 24-A
    2 24-B
    3 24-C
    4 25-A
    5 25-B
    6 26-A
    7 26-B

    I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?

    Any urgent help will be highly appreicated.

    Thanks
    mr_roomi

  2. #2
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Problem

    any suggestion on my problem?
    mr_roomi

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    possibly the reason no one answered within such a short time (it is courtesy not to bump your own question at all, but if you feel you must, give it more than just a few hours) is that your question is not very clear

    here's one solution:
    Code:
    create table pairs
    ( sr_no smallint not null 
    , week_no varchar(9)
    )
    
    insert into pairs values (1, '24-A')
    insert into pairs values (2, '24-B')
    insert into pairs values (3, '24-C')
    insert into pairs values (4, '25-A')
    insert into pairs values (5, '25-B')
    insert into pairs values (6, '26-A')
    insert into pairs values (7, '26-B')
    
    select one.week_no
         , two.week_no
      from pairs one
         , pairs two
     where one.sr_no 
         = two.sr_no - 1
      and floor(one.sr_no/2)
        < floor((one.sr_no+1)/2)
    
    24-A  24-B
    24-C  25-A
    25-B  26-A
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Sr. No

    I am controlling data with Week numbers instead. So i am trying to get data of 24 and 25 togather and so one. Sr. Number is nothing but increament field.

    Thanks for reply Mr. r937
    mr_roomi

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your data is very confusing

    how about showing some rows of real data, and then show what results you expect

    and which is it, oracle or sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here is your solution:
    Code:
    select week_no/2
         , amount
      from yourtable
    group
        by week_no/2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    week_no/2?

    You lost me on that one Rudy!

    Mr roomi, I think your problem is that your table does not follow basic rules of normalization. You are trying to store two values ("24", and "A", for instance) in a single field. This makes it difficult to sort or group by the column's components.

    Ideally, break the column into two different columns. If that is not an option, then you can simulate a "week_number" column using a formula:
    Select left(week_no, charindex('-', week_no)-1) as week_number

    (Double-check the syntax. I've been in Oracle recently and my TSQL is a little rusty...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    week_no/2?

    You lost me on that one Rudy!
    piece o' cake, my good man

    divide the week number by 2, and because it's an integer, the result is an integer

    so 22/2 is 11, 23/2 is 11, 24/2 is 12, 25/2 is 12, and so on

    this allows you to group the pairs of weeks -- 22 with 23, 24 with 25, and so on

    as for the normalization, check the other thread, it has better sample data, there's nothing wrong with the design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aw crap, i meant to cross link the guy's other post and forgot to

    http://www.dbforums.com/t1022906.html

    guy has two identical threads going on in separate forums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Jeez, a cross-post. This is a forum, not a freaking scavenger hunt.

    I was baffled how you were going to divide the string "24-A" by two...


    Mr. Roomi, here is a rule of thumb:
    "You cannot code a problem clearly and simply until you can state the problem clearly and simply."

    Really. I deal with people all too frequently who start coding without being able to define the problem, and they never get very far.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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