Select a result set that is a list of numbers from 1 to 100,000(Using Cartesian Join)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Select a result set that is a list of numbers from 1 to 100,000(Using Cartesian Join)

Hybrid View

  1. #1
    Join Date
    Apr 2008
    Posts
    3

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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 non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    You can take a table but it shouldn't contain 1 million rows.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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 non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2008
    Posts
    3
    I have tried this..

    select level
    from dual
    connect by level <= 100000

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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 non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Here's a hint: 00000+1...99999+1
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    May 2009
    Posts
    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.

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width