On Tue, 1999-10-05 20:03:48 -0400, Bob Kline wrote:
> On Tue, 5 Oct 1999, Hunter Hillegas wrote:
[...]
> > I have a list of songs in a database that I use to build a radio
> > playlist. Currently they come out in insertion order (or
> > something close) when I use 'select * from audio_files'...
> >
> > I would like them to come out in random order so I don't have to
> > shuffle the playlist externally... Just wondering if I could
> > handle that in the DB instead of doing it externally...
>
> Ok, in that case, go to http://www.egroups.com/list/mysql/ and enter
> the search string "Trouble with RAND function" to get to the thread
> others have been directing you to.
Maybe closer to what Hunter Hillegas is looking for is the recent
thread on "Choosing a random record".
The basic idea to get records in random order is to add a column with
random numbers and sort by it:
SELECT *, RAND() AS r FROM table ORDER BY r;
But due to a bug in older versions of MySQL, this doesn't work!
A work-around for versions of MySQL before V3.23 is:
SELECT *, id*0+RAND() AS r FROM table ORDER BY r;
where "id" is some field of your table.
Monty wrote:
| The problem is that without the 'id' MySQL 3.22 thinks that 'r' is
| an constant and will automaticly remove it from the ORDER BY clause.
Regards,
Martin
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7
