Sorting mySQL query - one order from multiple fields

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

Re: Sorting mySQL query - one order from multiple fields

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