Search code examples
javasqlnetbeans-7javadb

How to use the SQL RANDOM() function in a Netbeans JavaDB

I need to do a SQL call in a small local JavaDB in Netbeans 7.2 to pull a single random row from the database. 

SELECT * FROM JAVA2.FORTUNES ORDER BY RANDOM()

So far, I've got it to work using the RANDOM() function, but I'm having trouble getting LIMIT 1 to work, it returns a syntax error.

I know that every database has a different way to do this, and I can't figure out how it works specifically for this JavaDB in Netbeans (I got it to work in a separate Oracle DB with different syntax).

Is there a Java DB specific, or ANSII standard way to return a single row using the above syntax?

Solution

  • Java DB uses SQL's OFFSET/FETCH syntax to get this functionality.

    Assuming RANDOM() orders them correctly the following syntax should work:

    SELECT * FROM JAVA2.FORTUNES ORDER BY RANDOM() OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY