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-

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@lathspell.de]
> Sent: Friday, November 10, 2006 2:57 AM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> 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=j...e-infoshop.com
>
>