Iteration counter in SQL select statement

Do you mean like a row number?

You can add this to your select statement if you are using SQL 2005 or higher
   row_number() over (order by TableName.FieldName)

Not a row number.   More like pseudo-rows.   I don't want the Iteration counter tied to actual rows as it is in my example.

so you just want to get a list of numbers but not use a table?

That's correct - but it must be in a select statement

Not a table var.  I already have a solution that uses a table (IntegerNumber).  I am trying to get away from the table

Could you explain a bit more what you're trying to achieve?  What is the purpose of that query?  What is the resultset used for?

Valentino:

The reason I want such a query is to search for combinations of values that satisfy some requirement.

For example:

Select
   f = 10 * x.ii + 4 * y.ii + 3 * z.ii + 200, x.ii, y.ii, z.ii
from  IntegerNumbers as x, IntegerNumbers as y, IntegerNumbers as z
where
       10 * x.ii + 4 * y.ii + 3 * z.ii + 200 = 0
       and x.ii between -10 and -50
       and y.ii between -50 and 3000
       and z.ii between -10 and 60

There are many uses for a table like IntegerNumbers.  The question is:  how to get this functionality without actually having a permanent table (or temp table, or user defined table function, etc.).  As I mentioned before, is must be a select statement on - no loops, etc.



Brad:  I think you're right.  There probably isn't a way to do this.

Okay, so no tables and so on, just a select.  How about this recursive query using a Common Table Expression?
The snippet in attach returns values from 1 - 10.

with RecursiveCTE (ii)
as
(
	select 1 as ii
  UNION ALL
	select ii + 1 from RecursiveCTE
	where ii < 10
)
select * from RecursiveCTE

Select allOpen in new window

Valentino:

That looks pretty good.  I like that there is no formal temp table being defined.  Can this be changed to have specifc start and end points as parameters to RecursiveCTE?

Also, FYI:

A good use of this is as a unitless, generic dimension table.  Suppose, for example, that you wanted to show a list of dates and whether or not any invoice activity occured on the dates in question:

SELECT
     convert (datetime, '2008-01-24') + dates.ii,
     sum (Inv.amt) as Total
FROM IntegerNumbers as dates
left join Inv on Inv.InvoiceDate = convert (datetime, '2008-01-24') + dates.ii
where
    dates.ii between 0 and 6

Parameters are not possible afaik, but you could use variables, see snippet.

While testing this however I ran into a limitation which is probably an issue for you.  The maximum number of recursions in one execution is 100...

declare @startpoint int;
declare @endpoint int;
declare @interval int;
 
set @startpoint = 1;
set @endpoint = 100;
set @interval = 2;
 
with RecursiveCTE (ii)
as
(
        select @startpoint as ii
  UNION ALL
        select ii + @interval from RecursiveCTE
        where ii < @endpoint
)
select * from RecursiveCTE;

Select allOpen in new window

Unfortunately, the best I think I can hope for is a user defined table function.  Thanks for the suggestions guys.

Looks like the best I can hope for is a user defined table function.

I know the question is closed for some time but I couldn't help to poste. :o)

Here is a solution without table and without user function. It generates numbers up to 1000 but you can go to bigger numbers if you want just uncomment the commented lines and follow the pattern.

select 
	-- ...
	-- th.dg+
	h.dg*100+
	t.dg*10+
	u.dg+1 as number
from
( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u
cross join
( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t
cross join
( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h
-- cross join
-- ( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th
-- cross join 
-- ...
order by 
	number

Select allOpen in new window

One correction:

select
        -- ...
        -- th.dg*1000+
.
.
.

I put select 2 twice, not that would affect the results but still is superfluous. One more time :o)

select 
	-- ...
	-- th.dg*1000+
	h.dg*100+
	t.dg*10+
	u.dg+1 as number
from
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h
-- cross join
-- ( select 0 as dg union select 1 union select 2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th
-- cross join 
-- ...
order by 
	number

Select allOpen in new window

Thanks Zbertoc.  

After all this discussion, it seems the best approach is to just have a table with a bunch of numbers pre-populated in it.  That way, SQL doesn't have to "build" the numbers for you.  

Incidentally, the reason I started this whole discussion is because I read a book containing a number of unusual (and really good) SQL algorithms.   This IntegerNumber table was used in the book (although the author called it "Pivot").  The book is called the Transact-SQL Cookbook (O'Reilly Windows) (Mar 19, 2002) by Ales Spetic and Jonathan Gennick available via Amazon.  This is not your typical SQL book.  I highly recommend it.

Happy New Year!