MySQL order by primary key, put one row on top.

Asked by klaas4 (2194

) May 29th, 2011

Hi Flutherites!

I have a table in my MySQL database. Imagine just a standard table: id, val, plus one column named `active`, which is an ENUM(‘active’,‘not_active’).

There is either one or no record with `active` set to ‘active’. I want to sort my table using a query, so that the order is just standard (ORDER BY id ASC), but it puts the record with `active`=‘active’ on top, if available.

I’m using CodeIgniter if that helps anyone. My current query code is:

$query = $this->db->query(“SELECT * FROM presets”);

Maybe rearrange the $query-array instead of using a query if it can’t be done? I hope someone knows, otherwise I’ll have to write some workaround.

Thanks!
Davey.

Observing members: 0

Composing members: 0

3 Answers

If you can guarantee that there is only one or zero active records

select *
from presets
order by active asc, id asc

jaytkay (25810

)“Great Answer” (4

) Flag as…

What @jaytkay said. Another approach would be

SELECT * FROM table WHERE active = ‘active’
UNION
SELECT * FROM table WHERE active != ‘active’ ORDER BY ID

meiosis (6483

)“Great Answer” (1

) Flag as…

Thanks for your suggestions. @jaytkay I tried something like that, but I forgot to use those ASC properties. Only thing is, the active one ends up at the bottom. Fixed that by changing it to ”(...) order by active desc, id asc”. Works perfectly! Thank you.

@meiosis Sadly, this doesn’t. I get what you’re trying to do, but in this situation @jaytkay‘s suggestion is suffient. I am thankful you pointed me to UNION, it’s quite interesting. Thanks again!

klaas4 (2194

)“Great Answer” (0

) Flag as…