Search code examples
sqldatabasehsqldb

Select a random row from a db

I am looking for a solution which gives me a random row from a hsql db back.

CREATE TABLE Playlist(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
);

Any ideas?

UPDATE:

SELECT LIMIT 0 1 RAND(), p.name as foo
From Playlist p
ORDER BY foo

with this statement I get a random number back, but not a random playlist name.

Solution

  • You should go to How to request a random row in SQL?

    It covers a quite a few options on how to do what you need.

    SELECT p.name as foo
From Playlist p
ORDER BY RAND() LIMIT 1

    If you are using Oracle, you need a subselect using Rownum instead of limit. see How do I limit the number of rows returned by an Oracle query after ordering?