August 4, 2012 at 4:50 am
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
August 4, 2012 at 9:03 am
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
August 4, 2012 at 9:57 am
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
August 5, 2012 at 12:43 pm
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:
August 5, 2012 at 12:53 pm
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
Change is inevitable... Change for the better is not.
August 5, 2012 at 1:06 pm
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.
August 5, 2012 at 1:18 pm
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
August 5, 2012 at 10:33 pm
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 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
August 5, 2012 at 10:51 pm
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:
August 6, 2012 at 11:42 am
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
Change is inevitable... Change for the better is not.
August 6, 2012 at 11:46 pm
Didn't have to deal with gaps. The solutions here solved the issue. I have the list. Thanks to everyone!
August 8, 2012 at 7:18 pm
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
Change is inevitable... Change for the better is not.
August 8, 2012 at 7:31 pm
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 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
August 8, 2012 at 8:19 pm
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
Change is inevitable... Change for the better is not.
August 8, 2012 at 8:37 pm
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 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