Conditional ORDER BY

[s1mul4t3d]

I have would like to order my results one way if a condition is true, and another if it's false, and I'd like everything back in one recordset.

Right now, I'm just doing it as two statements, returning 2 separate recordsets:

SELECT published,title,weight,date FROM nodes WHERE published=true ORDER BY weight ASC
SELECT published,title,weight,date FROM nodes WHERE published=false ORDER BY date DESC

How can I combine these two into a single SELECT statement so they return one recordset? I want the results to look like this:


published title weight date
=========================================================
true MyFirstTitle 1 2009-02-10 10:28:00
true MySecondTitle 2 2009-02-13 10:15:00
true MyThirdTitle 3 2009-02-11 10:12:00
false MyFourthTitle 2 2009-02-11 10:45:00
false MyFifthTitle 2 2009-02-10 10:38:00
false MySixthTitle 1 2009-02-10 10:32:00In other words, I want all published nodes to appear in the top half of the list, ordered by weight, and I want all of the unpublished nodes to appear in the bottom half of the list, ordered by date.

[s1mul4t3d]

This worked in mySql.

SELECT published,title,weight,date, CASE WHEN published = true THEN 1 ELSE 2 END as Sort

FROM coupon

ORDER BY
CASE WHEN published = true THEN 1 ELSE 2 END,
CASE WHEN published = true THEN weight END ASC,
CASE WHEN published = false THEN date END DESC