4

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?

5

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|||||
  • 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 Sep 21 '17 at 15:22
  • 1
    @Gunnrryy try ORDER BY (resolution_id = 3 IS NOT TRUE), resolution_date ASC – Michael - sqlbot Sep 21 '17 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 Sep 21 '17 at 15:29
1

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
    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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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