Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51

    Unanswered: "SELECT TOP n..." legal in DB2?

    I know it's not SQL92 or SQL99 legal, but can you usethe TOP keyword in queries in DB2 such as:

    SELECT TOP 10 FROM orders

    It's legal in SQL Server and Oracle, but I can't seem to find any documentation to confirm that it can be used in DB2.

    ??

    TIA

  2. #2
    Join Date
    Apr 2003
    Posts
    23

    Arrow

    Do it like this:

    "SELECT * FROM orders FETCH FIRST 100 ROWS ONLY"

  3. #3
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    Is this the preferred method or the only method? I ask because we are going to need to port an process from SQL Server to DB2 and this is the only thing we did that wasn't SQL92 compliant. I'd prefer not to have to change it, but if necessary we will.

    -Loach

  4. #4
    Join Date
    Apr 2003
    Posts
    23
    Personally I don't know of any other way to do it...

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You should remember , with FETCH FIRST n .. , even if the data in your table does not change, DB2 will not gurantee you the same result for every execution of the statement ...

    To have a consistent result set, you may consider using ORDER BY ... Another function you can use is row_number() .... Check Birchall's SQL Cookbook (You can find the link in the thread 'Useful DB2 Stuff', the first or the second in the list)

    Cheers

    Sathyaram

  6. #6
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    With 'TOP n', if it's also used with an 'ORDER BY' cluase, a consistent result set is guaranteed. Can the 'FETCH FIRST N' cluase also be used in conjunction with an 'ORDER BY' clause to get this same consistency?

  7. #7
    Join Date
    Apr 2003
    Posts
    23
    Yes, that's correct.

    The FETCH FIRST will only fetch the first 100 results it finds, so if you need a particular order, definitley use ORDER BY.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    The sintax is:

    full select + FETCH FIRST n ROWS ONLY

    sample:
    SELECT C1, C2 FROM orders ORDER BY C1 FETCH FIRST 10 ROWS ONLY

    Hope this helps,
    Grofaty

  9. #9
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    Terrific! Thanks to you all!

Posting Permissions

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