Обсуждение: select a random record

Hi!

I'm currently thinking about building a completely database driven
jukebox. It will integrate with an already existing (well kind of) library
for my music files (and more).

This jukebox should offer a random play mode. It should take into account
when the title was played the last time. My Idea is to submit the query
returning the records ordered by their last time of play, ignore a random
number of records, fetch one record and drop the result set.

I'm wondering wether there is a more elegant way to fetch a random record
from a result.

Rainer

--
KeyID=759975BD fingerprint=887A 4BE3 6AB7 EE3C 4AE0  B0E1 0556 E25A 7599 75BD

On Sat, Sep 01, 2001 at 09:07:04AM +0200, Andre Schnabel wrote:
> The following select should do it:

Thanks! This is perfect. And I'm astonished how easy it is.


Rainer

--
KeyID=759975BD fingerprint=887A 4BE3 6AB7 EE3C 4AE0  B0E1 0556 E25A 7599 75BD

On Sat, 1 Sep 2001, Rainer Clasen wrote:

> This jukebox should offer a random play mode. It should take into account
> when the title was played the last time. My Idea is to submit the query
> returning the records ordered by their last time of play, ignore a random
> number of records, fetch one record and drop the result set.
>
> I'm wondering wether there is a more elegant way to fetch a random record
> from a result.

Sure, well, try this:

SELECT whatever FROM whereever WHERE whatever ORDER BY random() limit 5;

replace whatever and whereever with your selection criterie -- the order
by random() will randomise the results and limit 5 chooses only the first
5.
--
Tod McQuillin

----- Original Message -----
From: "Rainer Clasen" <bj@zuto.de>
Subject: [GENERAL] select a random record


...
>
> This jukebox should offer a random play mode. It should take into account
> when the title was played the last time. My Idea is to submit the query
> returning the records ordered by their last time of play, ignore a random
> number of records, fetch one record and drop the result set.
>
> I'm wondering wether there is a more elegant way to fetch a random record
> from a result.
>
> Rainer

The following select should do it:
SELECT title_name FROM titles
ORDER BY last_played DESC
LIMIT 1
OFFSET ( floor ( random () * 20);