.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
>
>
>
>
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]
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 t
Craig Hoffman wrote:
Hey Folks,
I have a query where it pulls random data and display's it.
SELECT route_photo, route, route_count, area FROM routes WHERE
ORDER BY RAND() LIMIT 1
The query works fine, however, the "route_photo" field is partially
populated. This results in just a "route" na
Donny Simonton wrote:
Neil,
We never delete from primary tables. No questions asked! We would just
mark a entry as deleted, and not select from it.
Another option you can do to solve your deletion problem is, select 35 rows
for example, when you really only want 30. That way, you can have extras
Donny Simonton wrote:
>
> Neil,
> We never delete from primary tables. No questions asked! We would just
> mark a entry as deleted, and not select from it.
>
> Another option you can do to solve your deletion problem is, select 35 rows
> for example, when you really only want 30. That way, you
age-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
> Neil Gunton
> Sent: Monday, March 08, 2004 3:11 PM
> To: Donny Simonton
> Cc: [EMAIL PROTECTED]; 'MySQL'
> Subject: Re: ORDER BY RAND() performance
>
> Donny Simonton wrote:
> > One other
Donny Simonton wrote:
> One other option that we use
> sometimes is say you need 30 results randomized, and you have an
> auto-increment in your table. Create 30 random numbers, then do a select
> with something like this:
>
> Select * from blabla where lkajsdlkjas IN (10, 43, 22, 8981, etc...)
>
Ray wrote:
> an alternative to the order by rand() with large record sets is to
> pick a random starting point "limit $randPoint, 30" don't know if
> its a viable solution to your situation, but it limits you to 2
> querys (row count, fetch) rather then the 30 (fetch 1 x 30)
Thanks! I did see thi
nton
> Cc: 'Neil Gunton'; 'MySQL'
> Subject: RE: ORDER BY RAND() performance
>
>
> Donny, what do you do? Throw all the values into an array or something
> on the client side, and use a random number generator to pull out the
> array elements?
>
>
On Monday 08 March 2004 14:14, Neil Gunton wrote:
> [EMAIL PROTECTED] wrote:
> > If your infact (sounds like) storing the pictures meta-data
> > (name, size, owner, etc) and the data (blob of some kind) .. I
> > would definately break up the design into 2 tables. That way
> > when dealing with the
roved.
>
> Donny
>
>
>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Monday, March 08, 2004 2:05 PM
> > To: Neil Gunton
> > Cc: MySQL
> > Subject: Re: ORDER BY RAND() performance
> >
> >
&g
[EMAIL PROTECTED]
> Sent: Monday, March 08, 2004 2:05 PM
> To: Neil Gunton
> Cc: MySQL
> Subject: Re: ORDER BY RAND() performance
>
>
> If your infact (sounds like) storing the pictures meta-data (name, size,
> owner, etc) and the data (blob of some kind) .. I would definately br
[EMAIL PROTECTED] wrote:
>
> If your infact (sounds like) storing the pictures meta-data (name, size,
> owner, etc) and the data (blob of some kind) .. I would definately break
> up the design into 2 tables. That way when dealing with the meta-data
> table (your RAND() query) there is much less d
If your infact (sounds like) storing the pictures meta-data (name, size,
owner, etc) and the data (blob of some kind) .. I would definately break
up the design into 2 tables. That way when dealing with the meta-data
table (your RAND() query) there is much less data that needs to be
traversed to g
ER BY MyOrder;
hth,
Arthur
- Original Message -
From: "Jamie Tibbetts" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 11, 2002 1:14 PM
Subject: Re: ORDER BY RAND() not working
> > $id = mysql_query("SELECT * FROM products WHERE sale_price
[snip]
PHP code:
$result = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL
ORDER BY RAND() LIMIT 3") or die(mysql_error());
The error message is as follows:
You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1
[/snip]
Using;
$query = "select * from tblBAR WHERE
> $id = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL ORDER
> BY RAND() LIMIT 3",$link) or die(mysql_error());
PHP code:
$result = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL
ORDER BY RAND() LIMIT 3") or die(mysql_error());
The error message is as follows:
What does your PHP code look like ? It might be a syntax problem inside PHP
-Message d'origine-
De : Jamie Tibbetts [mailto:[EMAIL PROTECTED]]
Envoye : jeudi 11 juillet 2002 18:22
A : [EMAIL PROTECTED]
Objet : ORDER BY RAND() not working
I'm running PHP 4.1.2 and MySQL 3.23.39. I ha
[snip]
I'm running PHP 4.1.2 and MySQL 3.23.39. I have a simple query that works if
I telnet into MySQL and run the query manually. If I try and use it in a PHP
page, I get the "Supplied argument is not a valid MySQL result resource"
error. However, if I take out the ORDER BY RAND() part from the
>> In a slight change of this question (since I have no data to
>> currently test this with, as my ISP is using too old a
>> version of mysql), does anyone know what something like
>> this would do?
>>
>> SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5;
>
> Exactly the same as SELECT * FROM
> Gotcha. So is there any way to return 5 (some number) rows, chosen
> randomly, and then sort them by date (or name or whatever). So the final
> result is a list, sorted by date, but of rows chosen randomly from the
> table.
CREATE TEMPORARY TABLE temptable TYPE=HEAP SELECT * FROM theTable ORD
> In a slight change of this question (since I have no data to
> currently test this with, as my ISP is using too old a
> version of mysql), does anyone know what something like
> this would do?
>
> SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5;
Exactly the same as SELECT * FROM theTable O
In a slight change of this question (since I have no data to currently test
this with, as my ISP is using too old a version of mysql), does anyone know
what something like this would do?
SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5;
I'd like to be able to pull out a certain number of row
> I don't think the answer has changed since last week :-)
>
> IMHO it's not "really slow and inefficient", anyway - this script:
(clipped)
> produces a table with 1 rows each containing an integer. Then we do:
>
> mysql> SELECT * FROM mytable ORDER BY RAND() LIMIT 5;
(clipped)
> 5 rows in set
You asked this earlier this week.
The answer has not changed.
Matt Heaton wrote:
> Hi all, trying to do something and have it be as efficient as possilble. My
> question is if I have a table with say 10,000 rows in it, and I issue
> a command like this
>
> select * from table where number=1 o
I don't think the answer has changed since last week :-)
IMHO it's not "really slow and inefficient", anyway - this script:
---start---
end
produces a table with 1 rows each containing an integer. Then we do:
mysql> SELECT * FROM mytable ORDER BY RAND() LIMIT 5;
+-+
| myfi
At 11:39 PM 2/10/2001 -0800, Stephen Waits wrote:
>Never mind on the "it doesn't work on my system" more like it didn't
>work on my brain :) Works fine.
Oh, phew.
>Theoretically it could be as fast as Carsten's method couldn't it? If
>it hit a record on the first shot? Otherwise it's pounding
"Jeffrey D. Wheelhouse" wrote:
>
> SELECT @lines:=COUNT(id) FROM table;
> SET @rand=CEILING(RAND()*@lines);
> SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;
Never mind on the "it doesn't work on my system" more like it didn't
work on my brain :) Works fine. And now that I ponder it a bit
"Jeffrey D. Wheelhouse" wrote:
>
> Here's another approach. I'm curious about the performance implications:
>
> SELECT @lines:=COUNT(id) FROM table;
> SET @rand=CEILING(RAND()*@lines);
> SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;
>
> This *should* give each row an equal chance, but it'
Hmm, no reading comprehension points for me. I managed to read Steve's
message the first time without realizing that he only wanted one row.
Here's another approach. I'm curious about the performance implications:
SELECT @lines:=COUNT(id) FROM table;
SET @rand=CEILING(RAND()*@lines);
SELECT
";
print $row[col2];
?>
Robert B. Barrington
GetMart Commercial Ecom: Web Administrator
http://weddinginlasvegas.com/
http://getmart.com/
[EMAIL PROTECTED]
Vegas Vista Productions
3172 North Rainbow Boulevard
Suite 326
Las Vegas, Nevada 89108-4534
Telephone: (702)656-1027
Facsimile: (702)656-
> Hi there,
>
> In the quest to get a random row from a table, "order by rand()" has
> proven too inefficient and slow. It's slow because MySQL apparently
> selects ALL rows into memory, then randomly shuffles ALL of them, then
> gives you the first one - very inefficient. There are a few other
Could you do something like:
CREATE TEMPORARY TABLE temptable (
pk INTEGER,
rand INTEGER
);
INSERT INTO temptable SELECT yourpk,Rand() FROM yourtable;
SELECT yourtable.* FROM yourtable,temptable WHERE pk=yourpk ORDER BY rand;
DROP TABLE temptable;
That might be quicker than your current a
34 matches
Mail list logo
