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