List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 18 1999 1:18pm
Subject:Re: SELECT Random sets possible?
View as plain text  
>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:

Martin> On Sat, 1999-09-18 02:57:53 +0300, Michael Widenius wrote:
>> >> Is it possible to SELECT a random recordset out of a database?
Martin> [...]
>> Another option is to do the following:
>> SELECT *,rand() as r from table ORDER BY r LIMIT 5
>> In this case you must just ignore the last column in your application.

Martin> For MySQL V3.22.19b (that's what I'm still using here) this doesn't
Martin> work!  One always gets the same five rows in the same order, though
Martin> column r shows different random numbers each time ...

Martin> I could get it to work by adding a table field to the rand() term:
Martin>   SELECT *, id*0+RAND() AS r FROM table ORDER BY r LIMIT 5;


Oops :(

Yes, you are right. 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.

SELECT Random sets possible?David Fallon16 Sep
  • Re: SELECT Random sets possible?Christian Mack17 Sep
    • Re: SELECT Random sets possible?Michael Widenius18 Sep
      • Re: SELECT Random sets possible?Martin Ramsch18 Sep
        • Re: SELECT Random sets possible?Michael Widenius19 Sep