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

Challenge

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2009-02-19 : 19:59:26

Is there any way to select numbers from 1 to 1000 without using temp table or insert statements?
Just a single select that will return numbers from 1 to 1000

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 20:05:57

Yes:

Select number from Master.dbo.spt_values
Where number between 1 and 1000 
and type = 'P'

nice123ej
Starting Member

48 Posts

Posted - 2009-02-19 : 20:30:47

No this is not what I meant
No access to master table
And what if the numbers are -100000 to 10000000 ?!

quote:

Originally posted by sodeep

Yes:

Select number from Master.dbo.spt_values
Where number between 1 and 1000 
and type = 'P'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-19 : 20:56:47

There is some useful information on this page: http://www.projectdmx.com/tsql/tblnumbers.aspx

The method using CTE is nice and simple - but you are limited to the maximum recursion depth which is 32767.


    WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 500 )
    SELECT n FROM Nbrs
    OPTION ( MAXRECURSION 500 )

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 21:11:43

quote:
Originally posted by nice123ej

No this is not what I meant
No access to master table
And what if the numbers are -100000 to 10000000 ?!

quote:
Originally posted by sodeep

Yes:

Select number from Master.dbo.spt_values
Where number between 1 and 1000
and type = 'P'

I just answered what you asked. If you don't have access to master db then you don't have access to SQL Server.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-20 : 03:20:47

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/06/easy-way-to-generate-number-table.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources