?
Solved

Iteration counter in SQL select statement

Posted on 2008-10-17
18
Medium Priority
?
4,229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
18 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22741226
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
ID: 22741293
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
ID: 22741644
so you just want to get a list of numbers but not use a table?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Author Comment

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

Assisted Solution

by:ValentinoV
ValentinoV earned 400 total points
ID: 22742468
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
ID: 22742898
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
ID: 22743312
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 1600 total points
ID: 22743344
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
ID: 22744203
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 22747770
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
ID: 22747855
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
ID: 22748129
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
ID: 31507134
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
ID: 22754103
Looks like the best I can hope for is a user defined table function.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23270417
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 27

Expert Comment

by:Zberteoc
ID: 23270426
One correction:

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

Expert Comment

by:Zberteoc
ID: 23270439
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
ID: 23275102
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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