Select a random row from a db

Question

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.

http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql covers this — Peter Wooster, Jan 12 '13 at 14:35
pls write it as an answer, so that I can except it. Thx for your solution!!! — maximus, Jan 12 '13 at 14:36
SO converted the short answer to a comment, it's now a bit longer as an answer. — Peter Wooster, Jan 12 '13 at 14:41

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

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?

edited May 23 '17 at 11:52

Community

answered Jan 12 '13 at 14:39

Peter Wooster

Thanks @bonCodigo, Note that the limit works well in MySQL, but not in oracle, there it gets much uglier. – Peter Wooster Jan 12 '13 at 15:07
1

Forgot to add the link.. ;) http://viralpatel.net/blogs/fetch-random-rows-from-database-mysql-oracle-ms-sql-postgresql-example/ – bonCodigo Jan 12 '13 at 15:45

1 Answers1