Thank you Rob. Meanwhile, I had submitted this same question to a DBA friend off list who offered virtually the exact same solution and it works. Thank you for your great response.
Originally I had hoped there was a GROUP BY method to achieve my end. But seems no. Also, I really did not want to use PHP array sorting cause I feel SQL is almost always the better solution.
Thanks all again.
Peter
-------- Original Message --------
Subject: Re: [mysql] How To Randomize This SQL Query Output
From: Rob Marscher <[email protected]>
Date: Wed, February 10, 2010 9:24 am
To: MySQL SIG <[email protected]>
On Feb 10, 2010, at 7:48 AM, Peter Sawczynec wrote:
> Given the below query, I need to have this come
> out order random by Region, but with Sort_Order and
> Sort_Name still ordered naturally.
>
> Any thoughts on this?
Join on a subselect that selects regions in random order. If you have a separate Region table, you can use that in the subselect. It might be a little difficult to tune this query. Probably want to cache it if there's a lot of data in the table. Other option would be to just select everything and do the sorting in php.
SELECT
DISTINCT WeddingAccommodation_Grid.Region, List_ID, Name, Address, Sort_Name, Thumb_File, Image_File,
Logo_File, Sort_Order
FROM WeddingAccommodation_Grid
JOIN (SELECT Region, Region * RAND() as Region_Order FROM WeddingAccommodation_Grid GROUP BY Region) Random_Region
ON WeddingAccommodation_Grid.Region = tmp.Region
WHERE 1=1
AND ( ( S1_Cabin <> 0 ) )
ORDER BY Random_Region.Region_Order, Sort_Order, Sort_Name
I think that will do it.
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
_______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
