RE: ORDER BY RAND() gives me duplicate rows sometimes

2006-11-10 Thread Jerry Schwartz 
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:[EMAIL PROTECTED]
> 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/[EMAIL PROTECTED]
>
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY RAND() gives me duplicate rows sometimes

2006-11-10 Thread uYe 
Add DISTINCT(primary_key)  in your query?

Regards


Willy

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY RAND() gives me duplicate rows sometimes

2006-11-09 Thread Christian Hammers 


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/[EMAIL PROTECTED]

ORDER BY RAND() gives me duplicate rows sometimes

2006-11-09 Thread Daevid Vincent 
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?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]