Solved

Iteration counter in SQL select statement

Posted on 2008-10-17
18
4,206 Views
Last Modified: 2012-06-27
I was wondering if anyone knows how to implement an iteration counter in SQL.  Heres what Im talking about:

Imagine you have a table called IntegerNumbers with a single field called ii.  The table is populated with values from 0 to 999999.

Now, in a select statement you can do this:

SELECT ii from IntegerNumbers WHERE ii between 1 and 10

Youll get a nice list of numbers out.

Basically, Im trying to do this but without the IntegerNumbers table.  Perhaps a VALUES statement?

If you know how to do this Id sure appreciate hearing about it.  A solution that will work in SQL 2005 would be preferred.

Thanks
 

0
Comment
Question by:DrLechter
  • 8
  • 4
  • 3
  • +1
18 Comments
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
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)

0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
so you just want to get a list of numbers but not use a table?
0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
That's correct - but it must be in a select statement
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 100 total points
Comment Utility
Do you mean a table variable?  See snippet

declare @tableVar table(

	ii int)
 

declare @i int

set @i = 1

while (@i < 11)

begin

	insert into @tableVar values (@i)

	set @i = @i + 1

end

select * from @tableVar

Open in new window

0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
Not a table var.  I already have a solution that uses a table (IntegerNumber).  I am trying to get away from the table
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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?
0
 
LVL 16

Accepted Solution

by:
brad2575 earned 400 total points
Comment Utility
I know what he is trying to do now but I do not beleive what you want to do is possible without at least writing a user defined function.
0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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

Open in new window

0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
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

0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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;

Open in new window

0
 
LVL 4

Author Closing Comment

by:DrLechter
Comment Utility
Unfortunately, the best I think I can hope for is a user defined table function.  Thanks for the suggestions guys.
0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
Looks like the best I can hope for is a user defined table function.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
One correction:

select
        -- ...
        -- th.dg*1000+
.
.
.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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

Open in new window

0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
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!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now