SQLServerCentral / Discuss Content Posted by Eli Leiba / Article Discussions / Article Discussions by Author / TSQL that lists all numbers from 1..100 / Latest Posts

RE: TSQL that lists all numbers from 1..100

Jeff Moden — Mon, 20 Feb 2012 06:12:54 GMT

[quote][b]ThomasGr (2/20/2012)[/b][hr]Try this one:with t as (select 1 x union all select x + 1 from t where x < 100)select x from t[/quote]Thomas, read the article at the link I provided in my last post and see why that's even worse than the original suggestion on this thread. Certainly, it's one of the worse ways of counting.

RE: TSQL that lists all numbers from 1..100

Cadavre — Mon, 20 Feb 2012 06:12:10 GMT

[quote][b]ThomasGr (2/20/2012)[/b][hr]Try this one:with t as (select 1 x union all select x + 1 from t where x < 100)select x from t[/quote][url=http://www.sqlservercentral.com/articles/T-SQL/74118/]Read the article that Jeff pointed at to see why that isn't a good idea.[/url]

RE: TSQL that lists all numbers from 1..100

ThomasGr — Mon, 20 Feb 2012 06:03:36 GMT

Try this one:with t as (select 1 x union all select x + 1 from t where x < 100)select x from t

RE: TSQL that lists all numbers from 1..100

Jeff Moden — Mon, 20 Feb 2012 05:55:30 GMT

[quote][b]GPO (2/20/2012)[/b][hr]Who was the first person to come up with this (Jeff's) idea in the context of SQL Server? Was it Itzik Ben-Gan?[/quote]Yes, it was Ben-Gan. He used a binary set of CTE's instead of a decimal set like many of us do here. If you'd like to see a comparison of some methods for counting, please see the following article.[url]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]

RE: TSQL that lists all numbers from 1..100

GPO — Mon, 20 Feb 2012 01:09:31 GMT

Who was the first person to come up with this (Jeff's) idea in the context of SQL Server? Was it Itzik Ben-Gan?

RE: TSQL that lists all numbers from 1..100

Jeff Moden — Sun, 19 Feb 2012 12:01:57 GMT

Gosh, Eli. Thanks for sharing but please consider the following, instead. It's faster, still doesn't require access to a table, can produce a much larger range of numbers, and takes a whole lot fewer rows of code especially for what it does.[code="sql"]WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10E1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E3 or 10000 rows E8(N) AS (SELECT 1 FROM E4 a, E4 b), --10E4 or 100000000 rowsE16(N) AS (SELECT 1 FROM E8 a, E8 b) --10E16 or more rows than you can shake a stick atSELECT TOP (@DesiredRowCount) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16;[/code]

TSQL that lists all numbers from 1..100

Eli Leiba — Sat, 18 Feb 2012 12:22:50 GMT

Comments posted to this topic are about the item [B]TSQL that lists all numbers from 1..100[/B]