Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 List of all numbers between low and high Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, August 04, 2012 4:50 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, February 16, 2014 7:17 PM Points: 231, Visits: 308
 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 ALLSELECT 2, 3 UNION AllSELECT 5, 8RESULTLow High Bet1 3 11 3 21 3 32 3 22 3 35 8 55 8 65 8 75 8 8`
Post #1340178
 Posted Saturday, August 04, 2012 9:03 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, February 16, 2014 7:17 PM Points: 231, Visits: 308
 Reworked another forum solution`DROP TABLE #TempBetCREATE TABLE #TempBet(Id int, Low int, High int)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 1, 3 UNION ALLSELECT 2, 2, 3 UNION AllSELECT 3, 5, 8; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, Result + 1FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result`
Post #1340191
 Posted Saturday, August 04, 2012 9:57 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, February 16, 2014 7:17 PM Points: 231, Visits: 308
 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 ALLSELECT 2, 20000000000, 30000000000 UNION AllSELECT 3, 50000000000, 80000000000; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, Result + 1FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result`
Post #1340196
 Posted Sunday, August 05, 2012 12:43 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, April 09, 2014 9:03 PM Points: 1,128, Visits: 1,161
 ` 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 05, 2012 12:53 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:00 PM Points: 36,016, Visits: 30,305
 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 ALLSELECT 2, 20000000000, 30000000000 UNION AllSELECT 3, 50000000000, 80000000000; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, Result + 1FROM InfoWHERE Result < High )SELECT * FROM InfoORDER 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1340285
 Posted Sunday, August 05, 2012 1:06 PM
 SSC Veteran Group: General Forum Members Last Login: Sunday, February 16, 2014 7:17 PM Points: 231, Visits: 308
 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 05, 2012 1:18 PM
 SSC Veteran Group: General Forum Members Last Login: Sunday, February 16, 2014 7:17 PM Points: 231, Visits: 308
 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 #TempBetCREATE TABLE #TempBet(Id int, Low bigint, High bigint)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 10000050000, 10000080000 UNION ALLSELECT 2, 10000070000, 10000090000 UNION AllSELECT 3, 50000050000, 50000090000 UNION AllSELECT 4, 50000060000, 50000070000 ; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, CAST(Result + 1 as bigint)FROM InfoWHERE Result < High )SELECT * FROM InfoORDER BY Id, Result`
Post #1340287
 Posted Sunday, August 05, 2012 10:33 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 5:42 PM Points: 3,596, Visits: 5,112
 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 ALLSELECT 2, 3 UNION AllSELECT 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=nFROM #TempBetCROSS APPLY ( SELECT n FROM Tally WHERE n BETWEEN Low AND High) aDROP 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 05, 2012 10:51 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, April 09, 2014 9:03 PM Points: 1,128, Visits: 1,161
 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 06, 2012 11:42 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 4:00 PM Points: 36,016, Visits: 30,305
 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 #TempBetCREATE TABLE #TempBet(Id int, Low bigint, High bigint)INSERT INTO #TempBet (Id, Low, High)SELECT 1, 10000050000, 10000080000 UNION ALLSELECT 2, 10000070000, 10000090000 UNION AllSELECT 3, 50000050000, 50000090000 UNION AllSELECT 4, 50000060000, 50000070000 ; WITH Info (Id, Low, High, Result) AS (SELECT Id, Min(Low), Max(High), Min(Low)FROM #TempBetGROUP BY Id UNION ALLSELECT Id, Low, High, CAST(Result + 1 as bigint)FROM InfoWHERE Result < High )SELECT * FROM InfoORDER 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1340787

 Permissions