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
