Return numbers in a range

Question

Return numbers in a range

alvaro-454996

SSC Enthusiast

Points: 115
More actions
June 27, 2013 at 9:10 am

#275606

Hi all, I have a table with 3 fields (index, startNumber, EndNumber) and i'm trying to make a select so it will return all number between startNumber and EndNumber, but just can't seem to find how, here's a more "graficl" view of what I want:
Table Numbers:
Index | StartNumber | End number
1 1000 1002
2 1550 1555
Desired output:
1000
1001
1002
1550
1551
1552
1553
1554
1555
Hope someone can help me.
Regards.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you usually solve this by creating a Tally or Numbers table that has all possilbe values, and then joining that agaisnt your data.

this is doing what you asked for, based on your sample data:

-- See how this starts off by creating a table

-- and inserting representative test data into it?

-- If you do this, it makes it a LOT easier for all

-- of the volunteers on this site to just copy/paste

-- this into a query window and start working on it.

DECLARE @test-2 TABLE ([Index] int,[StartNumber] int,[Endnumber] int)

INSERT INTO @test-2

SELECT 1,1000,1002 UNION ALL

SELECT 2,1550,1555

;WITH

-- This begins a virtual tally table. This query will be a LOT faster (< 1 second) with a permanent one.

-- See the article below for how to build one.

Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

-- Finally, return all the individual items

SELECT *

FROM Tally

CROSS JOIN @test-2 T1

WHERE N BETWEEN T1.StartNumber and T1.Endnumber

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mickyT SSChampion Points: 10360 More actions · Answer 2

Hi

Playing around with this, I came up with the following variation

WITH

Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

vTally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT t1.StartNumber + N

FROM #test t1

CROSS APPLY (SELECT TOP((t1.EndNumber - t1.StartNumber) + 1) N - 1 N FROM vTally) B;

To be honest I thought it would perform like a dog:-). but using the following setup to test it and using a physical Tally table rather than an inline one, I was surprised that it was slightly quicker that Lowell's solution and had less scans on the Tally table.

Using the query with an inline Tally table (as above) added a few seconds to it.

CREATE TABLE #test ([Index] int,[StartNumber] int,[Endnumber] int)

INSERT INTO #test

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) [Index]

, S

, s + D

FROM Tally

CROSS APPLY ( SELECT CAST(RAND(CHECKSUM(NEWID())) * 100000 AS INT) S, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) D ) A

DECLARE @i INT;

DECLARE @d DATETIME;

SET @d = GETDATE();

SELECT @i = t1.StartNumber + N

FROM #test t1

CROSS APPLY (SELECT TOP((t1.EndNumber - t1.StartNumber) + 1) N - 1 N FROM Tally) B;

SELECT DATEDIFF(ms,@d,GETDATE()); -- Approx 6000 ms

SET @d = GETDATE();

SELECT @i = N

FROM Tally

CROSS JOIN #test T1

WHERE N BETWEEN T1.StartNumber and T1.Endnumber;

SELECT DATEDIFF(ms,@d,GETDATE()); -- Approx 6600 ms

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

NM. Misread something.

--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 SSC Guru Points: 1004406 More actions · Answer 4

mickyT (6/27/2013)
To be honest I thought it would perform like a dog:-). but using the following setup to test it and using a physical Tally table rather than an inline one, I was surprised that it was slightly quicker that Lowell's solution and had less scans on the Tally table.
Using the query with an inline Tally table (as above) added a few seconds to it.

How many rows are in your physical Tally Table?

--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)

mickyT SSChampion Points: 10360 More actions · Answer 5

Jeff Moden (6/27/2013)
mickyT (6/27/2013)
To be honest I thought it would perform like a dog:-). but using the following setup to test it and using a physical Tally table rather than an inline one, I was surprised that it was slightly quicker that Lowell's solution and had less scans on the Tally table.
Using the query with an inline Tally table (as above) added a few seconds to it.
How many rows are in your physical Tally Table?

1,000,000

Bala' Ten Centuries Points: 1186 More actions · Answer 6

WITH RECURSIVE CTE

CREATE TABLE #TestTable ([Index] int,[StartNumber] int,[Endnumber] int)

INSERT INTO #TestTable

SELECT 1,1000,1002 UNION ALL

SELECT 2,1550,1555

;WITH TableCte

AS

(

SELECT [Index],[StartNumber] ,[Endnumber] FROM #TestTable

UNION ALL

SELECT [Index],[StartNumber]+1 ,[Endnumber] FROM TableCte A

WHERE [StartNumber]+1 NOT IN ( SELECT [StartNumber] FROM #TestTable)

and [StartNumber]+1 <= [Endnumber]

)

SELECT [StartNumber] from TableCte

ORDER BY [Index] , [StartNumber]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Bala' (6/28/2013)
WITH RECURSIVE CTE
CREATE TABLE #TestTable ([Index] int,[StartNumber] int,[Endnumber] int)
INSERT INTO #TestTable
SELECT 1,1000,1002 UNION ALL
SELECT 2,1550,1555
;WITH TableCte
AS
(
SELECT [Index],[StartNumber] ,[Endnumber] FROM #TestTable
UNION ALL
SELECT [Index],[StartNumber]+1 ,[Endnumber] FROM TableCte A
WHERE [StartNumber]+1 NOT IN ( SELECT [StartNumber] FROM #TestTable)
and [StartNumber]+1 <= [Endnumber]
)
SELECT [StartNumber] from TableCte
ORDER BY [Index] , [StartNumber]

There seems to be a redundant filter;

;WITH TableCte

AS

(

SELECT [Index],[StartNumber] ,[Endnumber]

FROM #TestTable

UNION ALL

SELECT [Index],[StartNumber]+1 ,[Endnumber]

FROM TableCte A

WHERE [StartNumber]+1 <= [Endnumber]

--and [StartNumber]+1 NOT IN ( SELECT [StartNumber] FROM #TestTable)

)

SELECT [StartNumber]

from TableCte

ORDER BY [Index] , [StartNumber]

Which would generate incorrect results if number ranges overlapped in the source table.

Note that rCTE's are an unnecessarily expensive option for generating rows and will perform poorly against tally-table based code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Bala' (6/28/2013)
WITH RECURSIVE CTE
CREATE TABLE #TestTable ([Index] int,[StartNumber] int,[Endnumber] int)
INSERT INTO #TestTable
SELECT 1,1000,1002 UNION ALL
SELECT 2,1550,1555
;WITH TableCte
AS
(
SELECT [Index],[StartNumber] ,[Endnumber] FROM #TestTable
UNION ALL
SELECT [Index],[StartNumber]+1 ,[Endnumber] FROM TableCte A
WHERE [StartNumber]+1 NOT IN ( SELECT [StartNumber] FROM #TestTable)
and [StartNumber]+1 <= [Endnumber]
)
SELECT [StartNumber] from TableCte
ORDER BY [Index] , [StartNumber]

Thanks for taking the time to share a solution. However, and to add to what ChrisM has already stated, that's a recursive CTE that counts to make a sequence. Please see the following article for why you should probably avoid such a technique.

http://www.sqlservercentral.com/articles/T-SQL/74118/

--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)

Bala' Ten Centuries Points: 1186 More actions · Answer 9

Thanks for this update Jeff. Yeah I got exactly what you have expressed.

I have an question ,is there any alternative way to get all child in self referencing table.

I have an application which has this kind of tables , and used rCTE often .

Example

CREATE TABLE dbo.MyEmployees

(

EmployeeID smallint NOT NULL,

FirstName nvarchar(30) NOT NULL,

LastName nvarchar(40) NOT NULL,

Title nvarchar(50) NOT NULL,

DeptID smallint NOT NULL,

ManagerID int NULL,

CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)

);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES

(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)

,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)

,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)

,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)

,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)

,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH MyCTE

AS ( SELECT EmployeeID , FirstName, LastName, ManagerID

FROM MyEmployees

WHERE ManagerID =273

UNION ALL

SELECT MyEmployees.EmployeeID , MyEmployees.FirstName, MyEmployees.LastName, MyEmployees.ManagerID

FROM MyEmployees

INNER JOIN MyCTE ON MyEmployees.ManagerID = MyCTE.EmployeeID

WHERE MyEmployees.ManagerID IS NOT NULL )

SELECT *

FROM MyCTE

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Bala' (6/30/2013)
Thanks for this update Jeff. Yeah I got exactly what you have expressed.
I have an question ,is there any alternative way to get all child in self referencing table.
I have an application which has this kind of tables , and used rCTE often .
Example...

I'm not suggesting you shouldn't use rCTE's at all, neither is Jeff. The point is that they are an expensive tool to use and should be used appropriately. Row generation isn't an appropriate use because there are far cheaper methods. Resolving parent / child hierarchies is appropriate because alternative methods are likely to be more expensive.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bala' Ten Centuries Points: 1186 More actions · Answer 11

Bala'

Ten Centuries

Points: 1186

July 1, 2013 at 2:07 am

#1628634

That's clear to me now.. Thanks Chris