SQL order by Date, but also custom ordering for Flight Number
===

I have the following situation:

![enter image description here][1]

I have ORDER BY Date DESC, FlightNumber DESC

I want this, except I want the canceled flights (for example: 870-C-2) to be below the successful flight of the same number.

For example, if flight 870 is cancelled twice and then flown successfully the same day, I want it to list it in the following manner:

    870
    870-C-2
    870-C-1


I believe I can do something if I do an ORDER BY CASE, except I don't know how. Any help would be greatly appreciated.

PS: Yes, the appended "-C-x" is a hacked attribute. It's how they set up their database before I got here, and I'm just an interning for the summer or else I'd redo their entire database schema.

  [1]: http://i.stack.imgur.com/MhJGi.jpg