10 Replies Latest reply: Feb 22, 2013 7:59 AM by 528063 RSS

    SQL quick challenge

    528063
      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
          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
            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
              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
                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
                  >
                  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
                    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
                      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
                        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
                          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
                            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!!!