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?

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

home work, huh?... what do you have so far?

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.

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 :)