List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:November 10 2006 2:32pm
Subject:RE: ORDER BY RAND() gives me duplicate rows sometimes
View as plain text  
The SELECT that Daevid originally tried is straight out of the
documentation, which says that ORDER BY RAND() LIMIT x is a good way to get
a random sample of your data.

The documentation also says you can't use a RAND() column in an ORDER BY
clause because the ORDER BY would evaluate the column multiple times. I'm
not sure what, exactly, the difference is between the two.

In any case, as I understand it your (Christian's) suggestion runs counter
to the documentation.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: Christian Hammers [mailto:ch@stripped]
> Sent: Friday, November 10, 2006 2:57 AM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: ORDER BY RAND() gives me duplicate rows sometimes
>
>
>
> On 2006-11-09 Daevid Vincent wrote:
> > I am using this query to pull three random comments from a table:
> >
> > "SELECT *, DATE_FORMAT(created_on, '%b %D') as date_format
> FROM comments
> > ORDER BY RAND() LIMIT 3";
> >
> > The problem is that sometimes, I get two of the same
> comment. How can I
> > refine this query to give me 3 unique/distinct ones?
>
> Maybe
>
> SELECT DISTINCT
>   rand() as rnd,
>   *,
>   DATE_FORMAT(created_on, '%b %D') as date_format
> FROM
>   comments
> ORDER BY
>   rnd
> LIMIT
>   3
> ;
>
> bye,
>
> -christian-
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>



Thread
ORDER BY RAND() gives me duplicate rows sometimesDaevid Vincent10 Nov
  • Re: ORDER BY RAND() gives me duplicate rows sometimesChristian Hammers10 Nov
    • RE: ORDER BY RAND() gives me duplicate rows sometimesJerry Schwartz10 Nov
  • Re: ORDER BY RAND() gives me duplicate rows sometimesuYe10 Nov