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