Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Question Unanswered: Select next available range

    I have the following table:
    ID - Reference - Number
    1 - null - 9000
    2 - null - 9001
    3 - 123 - 9002
    4 - 123 - 9003
    5 - null - 9004
    6 - null - 9005
    7 - null - 9006

    I need to return the next (TOP 3) consecutive rows where Reference is null. In this case, 9004-9006. I assume a "having" clause but I'm not sure how to do this.
    Thanks for any help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do you define "next" here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    4

    Next available

    I need the next available group of numbers where the reference column is null. In this case, the top 3 rows (where reference is null) would be 9000-9001, 9004. I need it to be 9004-9006. Basically, to fulfill the request with the first complete available range for a given quantity.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think maybe you didn't understand my question

    ID - Reference - Number
    1 - null - 9000
    2 - null - 9001
    3 - 123 - 9002
    4 - 123 - 9003
    5 - null - 9004
    6 - null - 9005
    7 - null - 9006
    8 - 123 - 9007
    9 - null - 9008
    10 - null - 9009
    11 - null - 9010

    which three are "next"?

    are you always starting with the lowest ID in the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2011
    Posts
    4
    the first group of 3 available (the lowest).
    9004-9006

  6. #6
    Join Date
    Dec 2011
    Posts
    4

    Solution found

    Quote Originally Posted by jlaurin View Post
    the first group of 3 available (the lowest).
    9004-9006
    Got the solution here:

    Select next available consecutive range

Tags for this Thread

Posting Permissions

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