Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In MySQL I have a table (simplified for this question)

CREATE TABLE `projects` (
  `title` varchar(255) NOT NULL,
  `priority` int(11) DEFAULT '1000000',
  `status` enum('new','in_progress','complete') DEFAULT 'new',
  PRIMARY KEY (`id`)

I want to select from the table ordered by priority but with rows where status is 'complete' to come last.

This works:

SELECT * FROM projects 
order by 
        when status='complete' then 999999
        when status!='complete' then priority 
    end ASC

but changing the 999999 to (select max(priority)+1 from projects) gives unexpected results, half way down the first page of results rows with status=complete appear.

Also this last method probably causes repeated calculation of the same value.

What is the best way to do this?

share|improve this question
up vote 3 down vote accepted

How about...

ORDER BY (`status` = 'complete'), `priority`

The expression status = 'complete' would resolve to 0 for non-complete, and 1 for complete... exactly the order you want... so you'd get all of the non-completed items sorted by priority, followed by all the completed items sorted by priority.

share|improve this answer
brilliant! thanks. – sdjuan Oct 23 '12 at 17:01

Using the IF() function, I converted your ORDER BY clause to the following:

ORDER BY IF(status='complete',999999,priority)

Give it a Try !!!

share|improve this answer
Isn't that essentially the same as the CASE expression in the question? – a_horse_with_no_name Oct 23 '12 at 6:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.