Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

List of all numbers between low and high Expand / Collapse
Author
Message
Posted Saturday, August 4, 2012 4:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
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





Post #1340178
Posted Saturday, August 4, 2012 9:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
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




Post #1340191
Posted Saturday, August 4, 2012 9:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
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






Post #1340196
Posted Sunday, August 5, 2012 12:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
 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
Post #1340284
Posted Sunday, August 5, 2012 12:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1340285
Posted Sunday, August 5, 2012 1:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
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.



Post #1340286
Posted Sunday, August 5, 2012 1:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
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






Post #1340287
Posted Sunday, August 5, 2012 10:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 3,417, Visits: 5,328
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!
Post #1340339
Posted Sunday, August 5, 2012 10:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1340343
Posted Monday, August 6, 2012 11:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1340787
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse