Search code examples
derby

Simple way to select first few rows in derby?

Derby doesn't have a rownum feature?

In oracle, I can select first 3 rows like the followings.

select * from a where rownum < 3

In here, they suggests like the followings. But it's tedious.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n
Solution

  • Note that Derby also supports the FETCH FIRST syntax, as described here: http://db.apache.org/derby/docs/10.11/ref/rrefsqljoffsetfetch.html#rrefsqljoffsetfetch

    Derby intentionally avoids non-standard SQL constructs. As you are learning, using such vendor-specific can be convenient, but it can also lead to non-portable applications.

    The advantage of using standard SQL syntax is that you are less likely to have compatibility problems when you move your application to another DBMS implementation. Derby's strong adherence to standard SQL can be a benefit in this case: if you develop your application using a DBMS like Derby, you are likely to be successful deploying your application on another, less standard DBMS.