Search code examples
javanetbeansderby

Values in Derby DB not ordering correctly

I am trying to only display the top ten values from a Derby database and tried various queries, none of which work. The first query I tried using first is:

Select * from scores order by pscore desc limit 5

However an error came up. The error code was:

Error code -1, SQL state 42X01: Syntax error: Encountered "limit" at line 1, column 43.

I then tried simplifying the query to:

Select * from scores order by pscore desc

This query works however the order is not correct. For example it orders the top five values as 75, 25, 200, 150, 125, 100

What is the correct query to use for Derby to only display the top 5 values in order?

Solution

  • Please refer to derby documentation regarding limit here

    Looks like it is not supported. Also workaround is given: use WHERE rownum <= 5 or FETCH for version 10.7+

    Corrected query:

    SELECT * FROM scores order by pscore desc FETCH FIRST 5 ROWS ONLY;