1


I need to create a range number from 1 to n.
For example, the parameter is @StartingValue

@StartingValue int = 96

Then the result should be:

Number
-------------
96
95
94
93
92
ff.
1

Does anyone have an idea how to do this?
Thank you.

Haminteu
  • 1,292
  • 4
  • 23
  • 49

4 Answers4

5

Use a Tally Table to generate the numbers:

DECLARE @N INT = 96

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
    SELECT TOP(@N) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E8
)    
SELECT * FROM CteTally ORDER BY N DESC

Explanation taken from Jeff's article (linked above):

The CTE called E1 (as in 10E1 for scientific notation) is nothing more than ten SELECT 1's returned as a single result set.

E2 does a CROSS JOIN of E1 with itself. That returns a single result set of 10*10 or up to 100 rows. I say "up to" because if the TOP function is 100 or less, the CTE's are "smart" enough to know that it doesn't actually need to go any further and E4 and E8 won't even come into play. If the TOP has a value of less than 100, not all 100 rows that E2 is capable of making will be made. It'll always make just enough according to the TOP function.

You can follow from there. E4 is a CROSS JOIN of E2 and will make up to 100*100 or 10,000 rows and E8 is a CROSS JOIN of E4 which will make more rows than most people will ever need. If you do need more, then just add an E16 as a CROSS JOIN of E8 and change the final FROM clause to FROM E16.

What's really amazing about this bad-boy is that is produces ZERO READS. Absolutely none, nada, nil.

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
2

One simple method is a numbers table. For a reasonable number (up to the low thousands), you can use spt_values:

with numbers as (
      select top 96 row_number() over (order by (select null)) as n
      from t
     )
. . .

Another method is a recursive CTE:

with numbers as (
      select 96 as n
      union all
      select n - 1
      from numbers
      where num > 1
     )

For larger values, you'll need to use the MAXRECURSION option.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

And another way.

SELECT N.number FROM  
  master..spt_values N 
WHERE  
  N.type = 'P' AND 
  N.number BETWEEN 1 AND 96
ORDER BY N.number DESC

More details on spt_values What is the purpose of system table master..spt_values and what are the meanings of its values?

Community
  • 1
  • 1
Liesel
  • 2,929
  • 2
  • 12
  • 18
  • If you are using `spt_values`, take note that your query may not work if the number required is bigger than max `number` in `spt_values`. `tally` or `recursive cte` is safer – Squirrel Apr 06 '16 at 02:59
0
Sequance of no's can be generated by following ways:
1. Using row_number by querying a large table and get the sequance.
2. Using system tables as you can see other people comments.
3. Using recursive CTE.

declare @maxValue int  = 96

; WITH rangetest AS 
(
    SELECT      MinValue =  @maxValue 
    UNION ALL
    SELECT      MinValue = MinValue - 1
    FROM        rangetest
    WHERE       MinValue > 1
) 
SELECT  *
from rangetest
OPTION  (MAXRECURSION 0)
Vinod Narwal
  • 169
  • 2