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 2005 Forums

Transact-SQL (2005)

Numbers

Author

Topic

muek
Starting Member

16 Posts

Posted - 2008-08-08 : 14:38:21

Hi,
is possible to create a query that gives me the number from 1 to 20?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 14:46:02

Yup

SELECT number
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND 20

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-08 : 15:27:59

If you don't want your code to be dependent on system tables in the master database, you should create your own table of sequential numbers within the application databases.
Or, for small sets of numbers, use this:


;
with NumList as
	(select 1 as NumVal
	UNION ALL
	select NumVal + 1
	from NumList
	where NumVal < 20)
select	*
from	NumList

e4 d5 xd5 Nf6

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-08 : 15:51:39

For larger datasets, this is still pretty fast:

;
with NumList (NumVal) as
		(select 0 as NumVal
		UNION ALL
		select NumVal + 1
		from NumList
		where NumVal < 9)
select	Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000
from	NumList Ones,
		NumList Tens,
		NumList Hundreds,
		NumList Thousands

e4 d5 xd5 Nf6

muek
Starting Member

16 Posts

Posted - 2008-08-11 : 04:25:04

Thanks that all I need.

I need to study "with" expression.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 04:38:31

SELECT 1 AS Number 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 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 05:14:15

quote:
Originally posted by muek

Thanks that all I need.

I need to study "with" expression.

its called CTE (common table expression). for more details, look into books online or refer link below

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=RecursiveCTE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 05:20:47

use this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

KH
[spoiler]Time is always against us[/spoiler]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-11 : 10:30:27

quote:
Originally posted by Peso

SELECT 1 AS Number 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 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 20

Elegant in its simplicity.

Boycott Beijing Olympics 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources