Creating a series of numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating a series of numbers

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2002-07-02 : 11:20:18

I need a select statement that will return a series of numbers, and have not been able to find examples for anything relevant.

Eg. A statement that will return 100 201 302 403 etc
A statement that returns all numbers between 1900 and 2002.

SQL wasn't exactly designed to do this, but can it be done?

Thanks,

jessop
Starting Member

9 Posts

Posted - 2002-07-02 : 11:22:34

yes it can be done - but you will need rules for your series and also to define the start and finish points - then loop through your rules

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-02 : 11:52:35

You can use the pivot table/auxilary table (basically a table of numbers) technique to play around with this type of queries.

For example, the following will create a table of 10000 numbers starting from 0.

--This is for temporary use

CREATE TABLE Prep (i char) GO

INSERT Prep
SELECT '0'
UNION
SELECT '1'
UNION
SELECT '2'
UNION
SELECT '3'
UNION
SELECT '4'
UNION
SELECT '5'
UNION
SELECT '6'
UNION
SELECT '7'
UNION
SELECT '8'
UNION
SELECT '9'
GO

--The following will create a numbers table
--with number 0 to 9999, using the table create above

SELECT CAST(a.i + b.i + c.i + d.i AS int) AS Number
INTO Numbers
FROM Prep a CROSS JOIN Prep b CROSS JOIN Prep c CROSS JOIN Prep d
GO

--The following index helps query performance

CREATE UNIQUE CLUSTERED INDEX NumbersIndex ON Numbers(Number)

--The following query will generate a particular
--series of numbers

SELECT Number+Diff
FROM Numbers CROSS JOIN (SELECT 101 AS Diff) AS Df
WHERE (Number/101.)-(Number/101) = 0 --I'm poor in math :-)

--The following query should generate the
--exact series you posted
SELECT CASE WHEN Number+Diff = 101 THEN 100 ELSE Number+Diff-1 END
FROM Numbers CROSS JOIN (SELECT 101 AS Diff) AS Df
WHERE (Number/101.)-(Number/101) = 0 --Poor in math again :-)

--
HTH,
Vyas
http://vyaskn.tripod.com

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-02 : 11:55:19

Silly me, try this WHERE clause instead:

SELECT CASE WHEN Number+Diff = 101 THEN 100 ELSE Number+Diff-1 END
FROM Numbers CROSS JOIN (SELECT 101 AS Diff) AS Df
WHERE Number%101 = 0 --Updated the WHERE clause

--
HTH,
Vyas
http://vyaskn.tripod.com

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-02 : 11:58:45

For one last time, here's the complete code required to produce the sequence 100, 201, 302, 403....

SET NOCOUNT ON
GO

CREATE TABLE Prep
(
i char(1)
)
GO

INSERT Prep
SELECT '0'
UNION ALL
SELECT '1'
UNION ALL
SELECT '2'
UNION ALL
SELECT '3'
UNION ALL
SELECT '4'
UNION ALL
SELECT '5'
UNION ALL
SELECT '6'
UNION ALL
SELECT '7'
UNION ALL
SELECT '8'
UNION ALL
SELECT '9'
GO

--The following will create a numbers table
--with number 0 to 9999, using the table create above

SELECT CAST(a.i + b.i + c.i + d.i AS int) AS Number
INTO Numbers
FROM
Prep a
CROSS JOIN
Prep b
CROSS JOIN
Prep c
CROSS JOIN
Prep d
GO

--This index will improve performance for some queries
CREATE UNIQUE CLUSTERED INDEX NumbersIndex ON Numbers(Number)

--Here's the query that generates the sequence
SELECT CASE Number WHEN 0 THEN 100 ELSE (Number+100) END AS [Sequence]
FROM Numbers
WHERE Number%101 = 0
ORDER BY [Sequence] ASC

--
HTH,
Vyas
http://vyaskn.tripod.com

Edited by - vyaskn on 07/02/2002 12:54:05

Edited by - vyaskn on 07/02/2002 16:39:46

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-02 : 18:17:46

Another way of doing it is at

http://www.queryplan.com/codelib.aspx

Michael

<Yoda>Use the Search page you must. Find answer you will.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-03 : 05:26:10

you could always do something like that in the 2nd part of the following link.....all you have to do is use variables for the start, end and increment-count values (1,8000,1 currently)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=17379

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources