SELECT all N between @min and @max

Question

I need to select all N numbers (integers) between @min and @max Is there any way to achieve this without using any loop?

Example: Let them say that @min = 5, @max = 9

I need the following values ​​returned by my SQL query: 5,6,7,8,9

(I am using MSSQL 2005)

Thanks!!

EDIT: This is a solution using a custom function that works fine. But there seems to be too much effort to sort through all the numbers manually. Thus, the question remains valid, achievable without a cycle.

CREATE FUNCTION GetAllNBetween ( @Min int, @Max int ) RETURNS @N TABLE(n int) AS BEGIN WHILE @Min <= @Max BEGIN INSERT INTO @N VALUES(@Min) SET @Min = @Min + 1 END RETURN END 

Used as follows:

 SELECT * FROM GetAllNBetween(5, 9) 

Answer 1

Request (as suggested by @Eric)

 select ROW_NUMBER() OVER (ORDER BY so1.id) from sysobjects so1,sysobjects 

Returns numbers from 1 to ~ 3000, in my almost empty test database. You can add another level of sysobjects to get an insane amount of lines. Then it’s a simple matter of filtering this

Answer 2

Well, I don't see the point of why you don't want to use a loop, but you can use recursion instead.

 DECLARE @min INT DECLARE @max INT SET @min = 5; SET @max = 12; WITH Nbrs ( n ) AS ( SELECT @min UNION ALL SELECT 1 + n FROM Nbrs WHERE n < @max ) SELECT n FROM Nbrs OPTION ( MAXRECURSION 500 ) 

It will generate a table with all values. Creating a string list from this should not be too complicated;)

Answer 3

You can do this using the between keyword. Here is an example.

 select * from tableName where e_id between (SELECT min(e_id) FROM tableName a) and (SELECT max(e_id) FROM tableName a) 

And if you know min and max, then directly put them instead of a subquery.

Answer 4

try the following:

 DECLARE @min int, @max int SELECT @Min=5,@Max= 9 SELECT TOP (@ Max-@Min +1) @Min-1+row_number() over(order by t1.number) as N FROM master..spt_values t1 CROSS JOIN master..spt_values t2 

OUTPUT:

 N -------------------- 5 6 7 8 9 (5 row(s) affected) 

see this previous question: What is the best way to create and populate a number table?

Answer 5

try it

 declare @min int set @min= (select 5) declare @max int set @max=(select 9) select * from table where id between @min and @max 

Answer 6

Can you use this row_number () function? This is new in Mssql 2005.

I also found out that you can also do this in ms sql 2005:

 select * from dbo.GetTableOfSequentialIntegers(100) where number between 5 and 9 

Answer 7

I have not seen answers with CTE (Common Table Expression), so here is one:

 WITH RECURSIVE integers(n) AS ( SELECT @min UNION SELECT n + 1 FROM integers WHERE n < @max ) SELECT n FROM integers