Page 1 of 1

Basic query syntax help please

Posted: Tue May 17, 2005 2:49 am
by thesimon
I have the following query

Code: Select all

SELECT *
FROM pressreleases
ORDER BY RAND() LIMIT 8
Which takes 8 random press releases, which is great, however i would like to change it so it selects the 8 lastest press releases, (it has a "orderdate" column with unix timestamps), then sorts them in a random order.

Posted: Tue May 17, 2005 2:54 am
by JayBird

Code: Select all

SELECT * FROM pressreleases ORDER BY orderdate DESC LIMIT 8

Posted: Tue May 17, 2005 2:55 am
by thesimon
thats great, thanks, however i need to randomise that selection, so take that 8 you have got and order it random

Posted: Tue May 17, 2005 3:03 am
by JayBird
think this will do it

Code: Select all

SELECT * FROM (SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT 8) pr_rand ORDER BY RAND()

Posted: Tue May 17, 2005 3:21 am
by thesimon
its spitting the dummy for some reason

Posted: Tue May 17, 2005 3:24 am
by JayBird
thesimon wrote:its spitting the dummy for some reason
what's the error?

Posted: Tue May 17, 2005 4:11 am
by thesimon
Error 1065 You have an error in your SQL syntax......to use near 'SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT

Posted: Tue May 17, 2005 4:27 am
by JayBird
ive just double checked it, works for me

Paste the exact SQL you are using

Posted: Tue May 17, 2005 5:08 am
by thesimon
Must be doing something wrong, just cant pick it.

Code: Select all

<?php
mysql_select_db($database_keenandb, $keenandb);
$query_stringwalk = "SELECT * FROM (SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT  pr_rand ORDER BY RAND()";
$stringwalk = mysql_query($query_stringwalk, $keenandb) or die(mysql_error());
$row_stringwalk = mysql_fetch_assoc($stringwalk);
$totalRows_stringwalk = mysql_num_rows($stringwalk);
?>
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT

Posted: Tue May 17, 2005 5:32 am
by thesimon
I changed it to

Code: Select all

(SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT 8)  ORDER BY RAND()
And it is now working beautifully. I cannot thank you enough for your help. You just saved me a long night(it will still be long, but i wont need to worry about that).