6

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?

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
3
  • i have a similar issue, i am trying ORDER BY (resolution_id = 3), resolution_date ASC but no kind of sorting is happening. i have to sort by resolution_date but on condition that resolution_id=3, want the ordered array for the rows having resolution_id =3, all the other resolution_id's dont matter. can you tell me where am i going wrong.
    – Gunnrryy
    Commented Sep 21, 2017 at 15:22
  • 1
    @Gunnrryy try ORDER BY (resolution_id = 3 IS NOT TRUE), resolution_date ASC
    – Michael - sqlbot
    Commented Sep 21, 2017 at 15:27
  • Thanks buddy, that worked. alternately ORDER BY (resolution_status_id = 3) ASC, resolution_date ASC did the trick too.. thanks a ton.
    – Gunnrryy
    Commented Sep 21, 2017 at 15:29
2

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

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

Give it a Try !!!

Improve this answer
1
  • 1
    Isn't that essentially the same as the CASE expression in the question?
    – user1822
    Commented Oct 23, 2012 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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