List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 7 1999 3:17am
Subject:Re: Outputting Selects in Random Order
View as plain text  
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
Thread
A question about indexesMoz5 Oct
  • Re: A question about indexesBob Kline5 Oct
    • Outputting Selects in Random OrderHunter Hillegas5 Oct
      • Re: Outputting Selects in Random OrderThimble Smith6 Oct
      • Re: Outputting Selects in Random OrderBob Kline6 Oct
        • Re: Outputting Selects in Random OrderScott Hess6 Oct
          • Re: Outputting Selects in Random OrderThimble Smith6 Oct
          • Re: Outputting Selects in Random OrderBob Kline6 Oct
            • Re: Outputting Selects in Random OrderHunter Hillegas6 Oct
              • Re: Outputting Selects in Random OrderBob Kline6 Oct
                • Re: Outputting Selects in Random OrderMartin Ramsch7 Oct
    • Re: Outputting Selects in Random OrderHunter Hillegas6 Oct
Re: A question about indexesMark Papadakis5 Oct
  • Re: A question about indexesBob Kline6 Oct