List of all numbers between low and high

  • What is the best way to get a list of all numbers between low and high numbers? Thanks.

    Actually >= low, <= high

    CREATE TABLE #TempBet

    (High int, Low int)

    INSERT INTO #TempBet (High, Low)

    SELECT 1, 3 UNION ALL

    SELECT 2, 3 UNION All

    SELECT 5, 8

    RESULT

    Low High Bet

    1 3 1

    1 3 2

    1 3 3

    2 3 2

    2 3 3

    5 8 5

    5 8 6

    5 8 7

    5 8 8

  • Reworked another forum solution

    DROP TABLE #TempBet

    CREATE TABLE #TempBet

    (Id int, Low int, High int)

    INSERT INTO #TempBet (Id, Low, High)

    SELECT 1, 1, 3 UNION ALL

    SELECT 2, 2, 3 UNION All

    SELECT 3, 5, 8

    ; WITH Info (Id, Low, High, Result) AS (

    SELECT Id, Min(Low), Max(High), Min(Low)

    FROM #TempBet

    GROUP BY Id

    UNION ALL

    SELECT Id, Low, High, Result + 1

    FROM Info

    WHERE Result < High )

    SELECT * FROM Info

    ORDER BY Id, Result

  • When I use larger numbers I get this error "types don't match between the anchor and the recursive part"

    CREATE TABLE #TempBet

    (Id int, Low numeric, High numeric)

    INSERT INTO #TempBet (Id, Low, High)

    SELECT 1, 10000000000, 30000000000 UNION ALL

    SELECT 2, 20000000000, 30000000000 UNION All

    SELECT 3, 50000000000, 80000000000

    ; WITH Info (Id, Low, High, Result) AS (

    SELECT Id, Min(Low), Max(High), Min(Low)

    FROM #TempBet

    GROUP BY Id

    UNION ALL

    SELECT Id, Low, High, Result + 1

    FROM Info

    WHERE Result < High )

    SELECT * FROM Info

    ORDER BY Id, Result

  • cast ((Result+1) as numeric)

    If you use Int ,in this case BigInt , then you won't be seeing this error...

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • texpic (8/4/2012)


    When I use larger numbers I get this error "types don't match between the anchor and the recursive part"

    CREATE TABLE #TempBet

    (Id int, Low numeric, High numeric)

    INSERT INTO #TempBet (Id, Low, High)

    SELECT 1, 10000000000, 30000000000 UNION ALL

    SELECT 2, 20000000000, 30000000000 UNION All

    SELECT 3, 50000000000, 80000000000

    ; WITH Info (Id, Low, High, Result) AS (

    SELECT Id, Min(Low), Max(High), Min(Low)

    FROM #TempBet

    GROUP BY Id

    UNION ALL

    SELECT Id, Low, High, Result + 1

    FROM Info

    WHERE Result < High )

    SELECT * FROM Info

    ORDER BY Id, Result

    The first item will produce a list of 20 Billion numbers, the second 10 Billion, and the 3rd 30 Billion for a total of 60 Billion numbers (rows).

    With that in mind, I have to ask... what is the purpose of this exercise and what do you expect to do with 60 Billion rows?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff:

    It was a bad example. The number of combinations between the true numbers is about 50,000 each. I'll try it with 50,000 each on the span and see if I get the error still.

  • I have a reference table that I was given that has 50,000 records. It has ranges between two 11-digit numbers. I need to makes sure there are no overlaps. I was going to try and get the list of all the numbers in between the two ranges, then simply make sure they were all distinct.

    For this example I cleaned up the ranges so they were less than 50,000 each; overlap is on purpose between the 2 lines. Changed all the numeric to bigint.

    New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

    -- DROP TABLE #TempBet

    CREATE TABLE #TempBet

    (Id int, Low bigint, High bigint)

    INSERT INTO #TempBet (Id, Low, High)

    SELECT 1, 10000050000, 10000080000 UNION ALL

    SELECT 2, 10000070000, 10000090000 UNION All

    SELECT 3, 50000050000, 50000090000 UNION All

    SELECT 4, 50000060000, 50000070000

    ; WITH Info (Id, Low, High, Result) AS (

    SELECT Id, Min(Low), Max(High), Min(Low)

    FROM #TempBet

    GROUP BY Id

    UNION ALL

    SELECT Id, Low, High, CAST(Result + 1 as bigint)

    FROM Info

    WHERE Result < High )

    SELECT * FROM Info

    ORDER BY Id, Result

  • You are probably better off using a Tally table for this:

    CREATE TABLE #TempBet

    (High int, Low int)

    INSERT INTO #TempBet (Low, High)

    SELECT 1, 3 UNION ALL

    SELECT 2, 3 UNION All

    SELECT 5, 8

    ;WITH Tally (n) AS (

    SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

    SELECT High, Low, Bet=n

    FROM #TempBet

    CROSS APPLY (

    SELECT n

    FROM Tally

    WHERE n BETWEEN Low AND High) a

    DROP TABLE #TempBet


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • texpic (8/5/2012)


    New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

    use MAXRECURSION hint.

    check this out:

    http://msdn.microsoft.com/en-us/library/ms186243(v=SQL.105).aspx

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • texpic (8/5/2012)


    I have a reference table that I was given that has 50,000 records. It has ranges between two 11-digit numbers. I need to makes sure there are no overlaps. I was going to try and get the list of all the numbers in between the two ranges, then simply make sure they were all distinct.

    For this example I cleaned up the ranges so they were less than 50,000 each; overlap is on purpose between the 2 lines. Changed all the numeric to bigint.

    New error now, "Msg 530, Level 16, State 1, Line 12 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

    -- DROP TABLE #TempBet

    CREATE TABLE #TempBet

    (Id int, Low bigint, High bigint)

    INSERT INTO #TempBet (Id, Low, High)

    SELECT 1, 10000050000, 10000080000 UNION ALL

    SELECT 2, 10000070000, 10000090000 UNION All

    SELECT 3, 50000050000, 50000090000 UNION All

    SELECT 4, 50000060000, 50000070000

    ; WITH Info (Id, Low, High, Result) AS (

    SELECT Id, Min(Low), Max(High), Min(Low)

    FROM #TempBet

    GROUP BY Id

    UNION ALL

    SELECT Id, Low, High, CAST(Result + 1 as bigint)

    FROM Info

    WHERE Result < High )

    SELECT * FROM Info

    ORDER BY Id, Result

    A recursive CTE may be a bit of overkill for this. Do you need to ensure there are no gaps, as well?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Didn't have to deal with gaps. The solutions here solved the issue. I have the list. Thanks to everyone!

  • texpic (8/6/2012)


    Didn't have to deal with gaps. The solutions here solved the issue. I have the list. Thanks to everyone!

    If you're using the "counting" rCTE that you posted before, you've likely built a performance problem into your code.

    What solution did you end up using?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/8/2012)


    texpic (8/6/2012)


    Didn't have to deal with gaps. The solutions here solved the issue. I have the list. Thanks to everyone!

    If you're using the "counting" rCTE that you posted before, you've likely built a performance problem into your code.

    What solution did you end up using?

    I second Jeff's question! We'd love to know what solution you settled on.

    Besides, I meant to post a slightly improved version of the tally table solution I suggested earlier anyway:

    CREATE TABLE #TempBet

    (High int, Low int)

    INSERT INTO #TempBet (Low, High)

    SELECT 1, 3 UNION ALL

    SELECT 2, 3 UNION All

    SELECT 5, 8

    DECLARE @MaxBet INT

    SELECT @MaxBet = MAX(High) FROM #TempBet

    ;WITH Tally (n) AS (

    SELECT TOP (@MaxBet) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

    SELECT High, Low, Bet=n

    FROM #TempBet

    CROSS APPLY (

    SELECT n

    FROM Tally

    WHERE n BETWEEN Low AND High) a

    DROP TABLE #TempBet


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (8/8/2012)


    Jeff Moden (8/8/2012)


    texpic (8/6/2012)


    Didn't have to deal with gaps. The solutions here solved the issue. I have the list. Thanks to everyone!

    If you're using the "counting" rCTE that you posted before, you've likely built a performance problem into your code.

    What solution did you end up using?

    I second Jeff's question! We'd love to know what solution you settled on.

    Besides, I meant to post a slightly improved version of the tally table solution I suggested earlier anyway:

    CREATE TABLE #TempBet

    (High int, Low int)

    INSERT INTO #TempBet (Low, High)

    SELECT 1, 3 UNION ALL

    SELECT 2, 3 UNION All

    SELECT 5, 8

    DECLARE @MaxBet INT

    SELECT @MaxBet = MAX(High) FROM #TempBet

    ;WITH Tally (n) AS (

    SELECT TOP (@MaxBet) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

    SELECT High, Low, Bet=n

    FROM #TempBet

    CROSS APPLY (

    SELECT n

    FROM Tally

    WHERE n BETWEEN Low AND High) a

    DROP TABLE #TempBet

    If "n" is, in fact, BETWEEN Low AND High, why do you need to use the extra clock cycles that ROW_NUMBER() will consume?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/8/2012)


    dwain.c (8/8/2012)


    Jeff Moden (8/8/2012)


    texpic (8/6/2012)


    Didn't have to deal with gaps. The solutions here solved the issue. I have the list. Thanks to everyone!

    If you're using the "counting" rCTE that you posted before, you've likely built a performance problem into your code.

    What solution did you end up using?

    I second Jeff's question! We'd love to know what solution you settled on.

    Besides, I meant to post a slightly improved version of the tally table solution I suggested earlier anyway:

    CREATE TABLE #TempBet

    (High int, Low int)

    INSERT INTO #TempBet (Low, High)

    SELECT 1, 3 UNION ALL

    SELECT 2, 3 UNION All

    SELECT 5, 8

    DECLARE @MaxBet INT

    SELECT @MaxBet = MAX(High) FROM #TempBet

    ;WITH Tally (n) AS (

    SELECT TOP (@MaxBet) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

    SELECT High, Low, Bet=n

    FROM #TempBet

    CROSS APPLY (

    SELECT n

    FROM Tally

    WHERE n BETWEEN Low AND High) a

    DROP TABLE #TempBet

    If "n" is, in fact, BETWEEN Low AND High, why do you need to use the extra clock cycles that ROW_NUMBER() will consume?

    Because idle machines are the devil's workshop? 🙂

    If you're suggesting that a true Numbers table would be faster then I'm with you. And I've seen some examples where you pull numbers directly out of a master table, like this one:

    SELECT n=number

    FROM [master].dbo.spt_values Tally

    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100

    Seeing as how I didn't know how big Bet could be I used the Tally CTE that I did.

    Are you suggesting something else?

    </eager-to-learn-more>


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply