In case anyone else was wondering, the command to use is COALESCE()
as in:
$theQuery = mysql_query("select variousFields from theTable where
date = '$currDate' ORDER BY COALESCE(rotime2,rotime1,time)");
COALESCE() will use "one" of the variables, being the one it finds a
value for first, for each record. Note how this differs from a multi
sort, which sorts by the first field, then subsorts by the second,
third, etc.
Hope this helps someone.
George Langley
Multimedia Developer, Audio/Video Editor, Musician, Arranger, Composer
http://www.georgelangley.ca
-------------------------------------------------
On 14-Jun-09, at 8:30 PM, George Langley wrote:
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.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
