[RESOLVED] ORDER BY CASE

[zzz]

I need to retrieve the results from my table in the following order

Active projects_status ASC
Expired projects_status DESC
Closed projects_status DESC

I know how to order by status:

ORDER BY CASE
		WHEN projects_status = 'active'   THEN 1
		WHEN projects_status = 'expired' THEN 2
		WHEN projects_status = 'closed'   THEN 3
		END

But when it comes to ASC and DESC I can't seem to be able to figure it out.

[bradgrafelman]

Your question doesn't make sense... what are you sorting in ascending or descending order? Some project name field or something?

Regardless, I don't think there is an easy way to do this in one query, so either:

1. sort all of the Active projects in ASC order and UNION the results with the Expired and Closed projects sorted in DESC order, or

2. retrieve the results into three arrays grouped by their statuses and then do the second sorting in PHP

[zzz]

Hmm, it's a bummer. I was hoping for some magic to make this sorted in a queue. It makes some sense because sometimes there are no active projects, so I would like to show expired, starting with the most recent, and is no more expired -- closed, starting with the most recent.

[dagon]

2 unions using your 3 where clause above would work if i understand the question.