Sunday, 25 May 2014

How to create number 1 to 100 using SQL query



With a simple recursive CTE:
;WITH nums AS
   (SELECT 1 AS value
    UNION ALL
    SELECT value + 1 AS value
    FROM nums
    WHERE nums.value <= 99)
SELECT *
FROM nums

Here's another option:
SELECT number+1 AS value
FROM master.dbo.spt_values
WHERE number <=99
AND Type = 'p'
ORDER BY number



No comments:

Post a Comment