[RESOLVED] Sorting by time closest to now

[amax]

Hi there, Id love some advice on this as am really stuck. Basically, I have a simple database of songs that Im creating a playlist from for a certain time of the day. Each song has a start time in the table - defined by mysql TIME format field of hh:mm:ss.

When my playlist.php file is queried, I simply want to return all the songs in the table where the start time>=now() appears first, followed by the rest of the songs after that in order from the next earliest start time again. I was trying:

SELECT songid,title, time FROM songs ORDER BY time >now(),time ASC

but its not giving me the results I desire. e.g. If I had 4 songs, with start times as follows:12:45,13:59,15:19, 16:00 and the time now was 15:45:00, I need to return 16:00:00, 12:45:00,13:59:00,15:19:00 and so on

Is this possible or am I going about this in totally the wrong way

[Derokorian]

This works for me:

SELECT * FROM timetest 
ORDER BY
  CASE
    WHEN `time` > CURTIME() THEN 1
    ELSE 0
  END DESC,
`time` ASC

My table looked like: CREATE TIME timetest (time TIME) with just 4 rows but it ordered up what appears to be the way you want.

[amax]

Thank you so much Derokorian, that works perfectly