This discussion is archived
10 Replies Latest reply: Feb 22, 2013 5:59 AM by 528063 RSS

SQL quick challenge

528063 Newbie
Currently Being Moderated
Hello All,

I have two tables:

Positions_tbl (Start_Position, End_Position)
X_Positions (X_Name, X_Position)

I need to find how many X_Position lies between each (Start_Position, End_Position)

Thank you!

Edited by: OMD on Feb 21, 2013 9:24 AM
  • 1. Re: SQL quick challenge
    bencol Pro
    Currently Being Moderated
    something like:
    select p.start_position
        ,p.end_position
       ,count(*)
    from positions_tbl p
    join x_positions x
      on  x.x_postion between p.start_position and p.end_position;
    ?
  • 2. Re: SQL quick challenge
    sb92075 Guru
    Currently Being Moderated
    OMD wrote:
    Hello All,

    I have two tables:

    Positions_tbl (Start_Position, End_Position)
    X_Positions (X_Name, X_Position)

    I need to find how many X_Position lies between each Start_Position, End_Position

    Thank you!
    what datatype are _Position columns?
    please post complete CREATE TABLE statements for both tables.

    Handle:     OMD
    Status Level:     Newbie
    Registered:     Aug 14, 2006
    Total Posts:     254
    Total Questions:     9 (8 unresolved)

    Now I understand why.
  • 3. Re: SQL quick challenge
    528063 Newbie
    Currently Being Moderated
    If you don't want to answer my question then keep your judgment for yourself, No need to post my info and and then as smart person say "Now I understand why". You definitely don't know why. I don't know who you are and you don't know me. So, just be respectful to the others.
  • 4. Re: SQL quick challenge
    Solomon Yakobson Guru
    Currently Being Moderated
    Use outer join:
    select  p.start_position,
            p.end_position,
            count(*)
      from      positions_tbl p
            left
                join x_positions x
              on x.x_postion between p.start_position and p.end_position
      group by p.start_position,
               p.end_position
      order by p.start_position,
               p.end_position
    /
    SY.
  • 5. Re: SQL quick challenge
    rp0428 Guru
    Currently Being Moderated
    >
    If you don't want to answer my question then keep your judgment for yourself, No need to post my info and and then as smart person say "Now I understand why". You definitely don't know why. I don't know who you are and you don't know me. So, just be respectful to the others.
    >
    Your forum history is public information that any registered user can see.

    As a forum user you are expected to mark your questions ANSWERED when they have been. When you don't do that it wastes the time of people that start reading the thread to try to help.

    When someone sees an unanswered thread and thinks they can help it is very frustrating to read the entire thread only to find out that it appears to have already been answered by one of the previous responders. The problem is that since you didn't mark the thread ANSWERED there is no way to tell if you are just being lazy or it really hasn't been answered and you need more help.

    Then when you cop an attitude like you just did in this response it only reinforces that maybe you are just being lazy and selfish; asking for help from others but not willing to acknowledge the help when you get it. That will cause some people to just not want to try to help you at all. So in addition to potentially wasting the time of others you are also really hurting yourself.

    So, if you really are a good steward of the forums and want to get help in the future I suggest that you revisit those previous threads and mark then ANSWERED if they have been.
  • 6. Re: SQL quick challenge
    528063 Newbie
    Currently Being Moderated
    Thanks Solomon, It worked on a sample data of my real tables.
    Now it is been running for like an hour on the real data (Positions_tbl = 30,000 rows , x_positions:30 million rows), still running!
  • 7. Re: SQL quick challenge
    kendenny Expert
    Currently Being Moderated
    Solomon Yakobson wrote:
    Use outer join:
    select  p.start_position,
    p.end_position,
    count(*)
    from      positions_tbl p
    left
    join x_positions x
    on x.x_postion between p.start_position and p.end_position
    group by p.start_position,
    p.end_position
    order by p.start_position,
    p.end_position
    /
    SY.
    Shouldn't that be count(x.x_position) rather than count(*)? Count(*) will show 1 when it should be 0.
  • 8. Re: SQL quick challenge
    kendenny Expert
    Currently Being Moderated
    OMD wrote:
    Thanks Solomon, It worked on a sample data of my real tables.
    Now it is been running for like an hour on the real data (Positions_tbl = 30,000 rows , x_positions:30 million rows), still running!
    Is there an index on x_position?
  • 9. Re: SQL quick challenge
    Solomon Yakobson Guru
    Currently Being Moderated
    kendenny wrote:
    Shouldn't that be count(x.x_position) rather than count(*)? Count(*) will show 1 when it should be 0.
    Yes, it should. Thanks for correction.

    SY.
  • 10. Re: SQL quick challenge
    528063 Newbie
    Currently Being Moderated
    Thanks kendenny, I could not notice with the tons of records I have. I have added an Index after the first run. Better now, It took 2 hours!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points