1

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
verystrongjoe
  • 3,831
  • 9
  • 35
  • 66

1 Answers1

2

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.

Bryan Pendleton
  • 16,128
  • 3
  • 32
  • 56
  • Fetch first row option is the one I want. But what if I want to select two more row? – verystrongjoe May 20 '15 at 23:24
  • 1
    You can fetch 3 rows, e.g., with "fetch first 3 rows". Or if you fetch 10 rows the first time, then want to fetch 10 more, do "offset 10 rows fetch next 10 rows". – Bryan Pendleton May 21 '15 at 00:18