Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Unanswered: Order by not working with limit/offset clause

    We are trying to use offset and limit in db2 SQL to fetch only chunk of records from the table and also trying to retrieve sorted results.
    Limit/offset clause is not working with sort clause. Is there a way to resolve this issue?

    Select column1, column 2 from table_name
    Where column1=<value>
    order by column1
    Limit 100 offset 0

    Note: we are using limit and offset clause like shown in below link
    https://www.ibm.com/developerworks/c...offset?lang=en

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please use "ROW_NUMBER() OVER(ORDER BY ...)" OLAP specification showed in the link(which was referenced by you).
    https://www.ibm.com/developerworks/c...offset?lang=en

    Let's look at each of those:

    ROW_NUMBER()
    Code:
    SELECT name, salary
      FROM (SELECT ROW_NUMBER() OVER(ORDER BY salary) AS rn,
                   emp.*
              FROM emp)
      WHERE rn BETWEEN 3 AND 5;
    NAME       SALARY
    ---------- -----------
    Brett            22000
    Martin           25000
    Murphy           30000
      3 record(s) selected.

Tags for this Thread

Posting Permissions

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