SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List of all numbers between low and high


List of all numbers between low and high

Author
Message
texpic
texpic
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 367
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






texpic
texpic
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 367
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





texpic
texpic
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 367
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







demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1517 Visits: 1192
 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85655 Visits: 41082
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
texpic
texpic
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 367
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.



texpic
texpic
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 367
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







dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7255 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1517 Visits: 1192
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85655 Visits: 41082
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search