Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 2 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
add comment

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
add comment

Your Answer

 
discard

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.