[RESOLVED] Select Random and Sort Results

digital_skwerl

I have a table which stores events for a calendar.

I would like to select 4 events at random and then sort those 4 results by date. Sounds easy, but my original query does not produce sorted results.

$query_events = "SELECT DISTINCT(events.vend_id), events.eve_id, events.eve_date FROM events, vendors 
WHERE events.eve_date BETWEEN '$today' AND '$future' AND events.eve_active='Y' AND vendors.vend_active='Y' AND events.vend_id=vendors.vend_id ORDER BY RAND(), events.eve_date LIMIT 4";

Notes:
1. Im using DISTINCT on the first column selected so that I am sure to get events from 4 different vendors
2. The WHERE clause contains variable 'today' and 'future' to retrieve only events within the next 30 days

I have since tried selecting 4 random events and dropping the results into an array, which I can then sort and loop through.

Is there a better way to select random entries and sort the results?

P.S. - I have read extensively about the limitations of RAND() on large tables, but for this applications I don't mind using it.

NogDog

I think you could use a sub-query:

SELECT * FROM (
   SELECT
      DISTINCT(events.vend_id),
      events.eve_id,
      events.eve_date
   FROM events, vendors
   WHERE
      events.eve_date BETWEEN '$today' AND
      '$future'                        AND
      events.eve_active='Y'            AND
      vendors.vend_active='Y'          AND
      events.vend_id=vendors.vend_id
   ORDER BY RAND()
   LIMIT 4
) AS randomized
ORDER BY eve_date

digital_skwerl

Thanks NogDog, well done. I need to get more familiar with subqueries.

For anyone else, here is my final code.

$query_event = "SELECT * FROM (SELECT DISTINCT(events.vend_id), events.eve_id, events.event, events.eve_intro, DATE_FORMAT(events.eve_date, '%b %e, %Y') AS evedate, events.eve_cost, events.eve_time, events.eve_loc_short, vendors.vendor FROM events, vendors 
WHERE events.eve_date BETWEEN '$today' AND '$future' AND events.eve_active='Y' AND vendors.vend_active='Y' AND events.vend_id=vendors.vend_id ORDER BY RAND() LIMIT 4) AS randomized 
ORDER BY evedate";