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]
