Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

From: Jeremy Rickard <jrickard_at_unisystems.biz>
Date: 18 Sep 2002 04:07:57 -0700
Message-ID: <d36116ef.0209180307.619122c3@posting.google.com>

Philip Nelson <pandp_at_attglobal.net> wrote in message news:<pan.2002.09.17.13.25.07.407715.2181_at_attglobal.net>...
> Steve,
>
> Here's (from memory) what DB2 offers in this area.
>
> It's called the "FETCH FIRST n ROWS" clause.
>
> So you would code -
>
> SELECT username FROM users_table ORDER BY username FETCH FIRST 10 ROWS
> ONLY
>

It's also possible to fetch any "window", rather than just the first n rows, using an OLAP function, e.g.:

SELECT
  a.username
FROM
  (
    SELECT

      ROW_NUMBER() OVER (ORDER BY username) AS row_number,
      username
    FROM
      users_table

  ) AS a
WHERE
  a.row_number BETWEEN 11 AND 20;

Jeremy Rickard Received on Wed Sep 18 2002 - 06:07:57 CDT