2

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

Example: Let's say @min = 5, @max = 9

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

(I'm using MSSQL 2005)

Thanks!!

EDIT: This is a solution using a custom function, which works fine. But it seems too much effort having to loop through all numbers manually. So the question is still, whether it's achievable without a loop.

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

To be used like this:

SELECT * FROM GetAllNBetween(5, 9)
Sam7
  • 3,382
  • 2
  • 34
  • 57

7 Answers7

2

Well I don't see the point why you don't want to use a loop, but you can use a 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. Generating a string list out of this shouldn't be too hard ;)

user503034
  • 49
  • 1
2

The query (as suggested by @Eric)

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

Returns the numbers between 1 and ~3000, on my mostly empty test database. You can add another level of sysobjects to get an insane number of rows. Then it's a simple matter of filtering this

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

You can do it using the between keyword. Here is the 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 those instead of nested query.

ahsteele
  • 26,243
  • 28
  • 134
  • 248
Rupeshit
  • 1,476
  • 1
  • 14
  • 23
  • Well the problem is that there is no table that exists with the entries. So 'tableName' doesn't exists in my scenario. – Sam7 Nov 15 '10 at 07:03
1

try this:

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 numbers table?

Community
  • 1
  • 1
KM.
  • 101,727
  • 34
  • 178
  • 212
0

I haven't seen any answers with a CTE (Common Table Expression), so here's one:

WITH RECURSIVE integers(n)
AS (
  SELECT @min
  UNION SELECT n + 1 FROM integers WHERE n < @max
) SELECT n FROM integers
Peter Martini
  • 133
  • 4
  • I wasn't even aware of CTEs. I looks very promising. Except I can't get the query you provided to work. Are you able to provide a complete sample, please? – Sam7 Dec 15 '16 at 00:28
0

Try this

declare @min int

set @min= (select 5)

 declare @max int

set @max=(select 9)

select * from table

where id between @min and @max
anishMarokey
  • 11,279
  • 2
  • 34
  • 47
  • Well the problem is that there is no table that exists with the entries. So 'table' doesn't exists in my scenario. – Sam7 Nov 15 '10 at 07:04
  • Look at my answer using the function. That's what I'm getting at. Except without the function ;) – Sam7 Nov 15 '10 at 07:21
0

Can you make use of this function row_number()? It's new in Mssql 2005.

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

select *
  from dbo.GetTableOfSequentialIntegers(100)
 where number between 5 and 9
ahsteele
  • 26,243
  • 28
  • 134
  • 248
Eric K Yung
  • 1,754
  • 11
  • 10