SQL list of numbers 1-100
R. Soby@discussions.microsoft.com Jason R. Soby
Right now, i can only think of
Select '1'
UNION
Select '2'
...All the way to 100. Is there a good, efficient, way of doing this other
than 100 unions? Thanks!
Jason
Plamen Ratchev
WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4)
SELECT n FROM Nums
WHERE n <= 100;
--
Plamen Ratchev
http://www.SQLStudio.com
Bob
;WITH cte AS
(
SELECT 1 x
UNION ALL
SELECT x + 1
FROM cte
WHERE x < 100
)
SELECT x
FROM cte
Jason R. Soby
I'm a newbie at SQL
Plamen Ratchev
using only 2 CTEs first, then adding one more, etc. That is a very efficient technique.
Bob's solution uses a recursive CTE. In essence it is a loop that increments with each recursive call.
Uri Dimant
Another one using CROOS JOIN command
WITH digits AS (
SELECT 0 as Number
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
)
SELECT (tens.Number * 10) + ones.Number +1 as Number
FROM digits as tens
CROSS JOIN digits as ones
ORDER BY Number
"Jason R. Soby" <Jason R. So...@discussions.microsoft.com> wrote in message
news:60BC4144-5A94-48A5...@microsoft.com...
Jason R. Soby
Jason R. Soby
either of the queries. i dont understand the logic enough to manage on my own
:)
Thanks so much for ya'lls help
Plamen Ratchev
WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num5)
SELECT (n - 1) * 0.25
FROM Nums
WHERE n <= 100 * 4 + 1;
Jason R. Soby
--CELKO--
You can literally do that:
CREATE VIEW OneHundred(seq)
AS
SELECT i
FROM (VALUES (1), (2), ..(100)) AS X(i);
Plamen Ratchev
generate sufficient volume of numbers.
Tom Cooper
table. See
http://www.aspfaq.com/show.asp?id=2516
for what the are and why you want one.
Once you have created the Numbers table, then your first query is just
Select Number
From Numbers
Where Number <= 100
Order By Number;
and your second query is
Select Number * 0.25
From Numbers
Where Number <= 400
Order By Number;
Also, note that Joe Celko's solution only works if you are on SQL Server
2008. It uses a new feature that does not exist in previous versions of SQL
Server.
Tom
"Jason R. Soby" <Jason R. So...@discussions.microsoft.com> wrote in message
news:60BC4144-5A94-48A5...@microsoft.com...
Uri Dimant
How much does it take typing values till 10000000 for example? :-))) Are
you serious Joe?
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:3c5d8abb-499d-4892...@i6g2000yqj.googlegroups.com...
--CELKO--
If he wants a different table of constants, we can try a different
approach. I also don't do as lot of repetitive typing -- there are
tools for that,
You missed my point; SQL is a declarative data language and not a
computational one. Even in your procedural mindset, would write a
loop to compute pi every place you use it? Or would you declarative
it once as a global constant?
Uri Dimant
>approach. I also don't do as lot of repetitive typing -- there are
>tools for that,
heheheh like
INSERT INTO tbl DEFAULT VALUES
GO 10000
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:deb44c78-868d-470e...@d32g2000yqh.googlegroups.com...
Jason R. Soby
of a percent eg: 0.01 and it's friendly name equivalent 1% from 1 > 100 in
increments of .25.
Paddy
Go
INSERT INTO #tbl DEFAULT VALUES
GO 100
go
select * from #tbl
go
create table #tbl2(i decimal (10,2))
Insert into #tbl2 Values (.00)
Insert into #tbl2 Values (.25)
Insert into #tbl2 Values (.5)
Insert into #tbl2 Values (.75)
select
a.i + b.I as DecimalValue,
Cast((a.i + b.I) as Varchar(10)) + ' Percentage' as 'FriendlyName'
From
#tbl a,
#tbl2 b
Order By 1
drop table #tbl2
drop table #tbl