Sorting mySQL query - one order from multiple fields

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all. Am trying to sort baseball games by time, where there can be up to 3 times listed per game. Each game has an original "date" and "time" field, plus fields for 2 rain-out dates/times ("rodate1" "rotime1", "rodate2", "rotime2"), to use if the game gets rained out. Note that rotime1 and rotime2 are NULL if no time has been entered. Also note that the original date and time fields are not changed - they are kept for posterity. Usually, the rain-out date is set to a day that the teams were already going to play each other again, with the rain-out game going first. So need to sort those 2 games in order: rain-out first, then normally-scheduled. But, I can't just sort on the "time" field, as the rain-out game could now have a different time. I need to use the rotime2 (if it exists), else use the rotime1 (if it exists), else use the time.
	Can not get my query order to work. One of the variations I've tried:

$theQuery = mysql_query("select variousFields from theTable where date = '$currDate' ORDER BY CASE WHEN rotime2 THEN rotime2 WHEN rotime1 THEN rotime1 ELSE time");

Is there a query sort that will work in this case? Is not the usual "sort by last name, then sort by first name" scenario!
	Thanks for any pointers.


George Langley
Multimedia Developer, Audio/Video Editor, Musician, Arranger, Composer

http://www.georgelangley.ca


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux