MySQL order by – one column conditional on another column

MySQLorder-by

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

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

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 
    case 
        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?

Best Answer

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.

Related Question