SQL Query to fetch numbers in given steps between a range

Question

I have a set of data like this:

MinNo: 2500
MaxNo: 2700
IncrementStep: 10

Between the minimum number and the maximum number a list of all possible numbers with the given step are to be listed as shown below:

I'm aware that this can be achieved using a while loop. Kindly let me know if this can be done using a select query using Common Table Expressions (if needed). Thanks in advance.

score 5 · Accepted Answer · answered Dec 21 '11 at 08:00

5

You can use a numbers table (or master..spt_values).

declare @MinNo int
declare @MaxNo int
declare @IncrementStep int

set @MinNo = 2500
set @MaxNo = 2700
set @IncrementStep = 10

select @MinNo + Number * @IncrementStep
from master..spt_values
where type = 'P' and
      number between 0 and (@MaxNo - @MinNo) / @IncrementStep

Or a recursive CTE

;with C as
(
  select @MinNo as Num
  union all 
  select Num + @IncrementStep
  from C
  where Num < @MaxNo
)      
select Num
from C

answered Dec 21 '11 at 08:00

Mikael Eriksson

136,425
22
210
281

Talking about Jeff Moden, here's an answer explaining why a recursive CTE is probably never a good solution to this type of problem: http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers (but then again, the best-performing solution is always to use a physical numbers table anyway) – Tao Dec 21 '11 at 08:46

score 1 · Answer 2 · answered Dec 21 '11 at 08:11

See this useful function

CREATE FUNCTION [dbo].[Sequence](@min INT, @max INT, @step INT)
RETURNS @ret TABLE (id INT PRIMARY KEY)
AS
BEGIN
    WITH numbers(id) as
    (
        SELECT @min id
        UNION ALL 
        SELECT id+@step
        FROM numbers
        WHERE id < @max
    )
    INSERT @ret 
    SELECT id FROM Numbers
    OPTION(MAXRECURSION 0)
    RETURN
END

Tao · Answer 3 · 2011-12-21T08:35:36.110

@Mikael Eriksson already mentioned a numbers table / tally table (search for it online, there are LOTS of possible uses, many DBAs always want a tally table to be present in any system they manage)

I just wanted to share one non-recursive CTE-based "tally table" solution that I saw online the other day, that I think it amazingly elegant for its huge range (4 thousand million logical rows) and general applicability without any database dependencies:

WITH 
        E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT *
FROM cteTally
WHERE N >= 2500 
    AND N <= 2700
    AND N % 10 = 0

I found it here, but I don't know whether that's the original source of this CTE.

The nice thing about it is that you don't need to worry about min, max, or step size, and yet it performs very well in most (one-off) situations. That said, it should NOT be used in any frequently-called business process; Any physical indexed numbers table will always perform better!

EDIT: I just searched a little more for the source of this method (I had missed the stackoverflow link in the article I referenced), and apparently it's originally attributed to Itzik Ben-Gan, from the bottom of page 255 in a book titled "Inside Microsoft SQL Server 2005 - T-SQL Querying" (says Jeff Moden, who I implicitly trust).

SQL Query to fetch numbers in given steps between a range

3 Answers3

Linked