
Select a result set that is a list of numbers from 1 to 100,000(Using Cartesian Join)
I’m looking for a way to get a SQL result set that is a list of numbers from 1 to 100,000 (one hundred thousand [or one lakh]). I don’t want to create a table (temporary or otherwise) with 100,000 rows in it. (This would give me my solution, but I don't want to have to create such a large table.) Also, it needs to work in Oracle, PostgreSQL, MySQL, Microsoft SQL Server, and DB2, so it can’t use any proprietary SQL functions or predicates; it should be standard SQL. Also, the solution should be elegant, that is, not overly complex or difficult to understand. Can anyone help me?
OK, here's a hint. I use a small helper table, like this...
create table digits (
digit numeric(1)
);
insert into digits values(0);
insert into digits values(1);
insert into digits values(2);
insert into digits values(3);
insert into digits values(4);
insert into digits values(5);
insert into digits values(6);
insert into digits values(7);
insert into digits values(8);
insert into digits values(9);
commit;
Thanks

so... you don't want to use a table but your hint includes a table.
Don't you think that's kind of confusing?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test nonproduction environment advisor should not held liable or responsible for any actions taken based on the given advice.

You can take a table but it shouldn't contain 1 million rows.

home work, huh?... what do you have so far?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test nonproduction environment advisor should not held liable or responsible for any actions taken based on the given advice.

I have tried this..
select level
from dual
connect by level <= 100000

Okay, that's pretty compact and neat solution which would work great on Oracle yet don't think it complies with all the constraints described on your specs.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test nonproduction environment advisor should not held liable or responsible for any actions taken based on the given advice.

Here's a hint: 00000+1...99999+1

Fun problem found on a google search when I was looking for a way to do the same thing.
Here's the answer 
select 1 from dual union
select 2 from dual union
select 3 from dual union
...
select 100000 from dual
Just kidding! ...
Here's the answer you're probly looking for
select 1 + a.digit * 10000 + b.digit * 1000 + c.digit * 100 + d.digit * 10 + e.digit
from digits a, digits b, digits c, digits d, digits e
order by 1 + a.digit * 10000 + b.digit * 1000 + c.digit * 100 + d.digit * 10 + e.digit
Also, if you don't want to have to create the "helper" table, but there is a table that you know has at least 10 rows (call it table10), you can do something like this
select 1 + a.digit * 10000 + b.digit * 1000 + c.digit * 100 + d.digit * 10 + e.digit
from (select rownum  1 digit from table10 a where rownum <= 10) a
, (select rownum  1 digit from table10 a where rownum <= 10) b
, (select rownum  1 digit from table10 a where rownum <= 10) c
, (select rownum  1 digit from table10 a where rownum <= 10) d
, (select rownum  1 digit from table10 a where rownum <= 10) e
order by 1 + a.digit * 10000 + b.digit * 1000 + c.digit * 100 + d.digit * 10 + e.digit
But I don't know if this would work in other databases besides oracle.

Well, since it's been long enough that we're not helping him or her to cheat anymore...
The approach is correct, although, as you feared, the implementation does not translate. I know of no way to do an actual or fake row number that will work on all databases.
So, you have to use the table that he proposed (well, that his teacher proposed) to generate the digits. But the rest is correct  although it would be clearer if you did the +1 at the end
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
