General Question

klaas4's avatar

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

Asked by klaas4 (2166 points ) 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

jaytkay's avatar

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

select *
from presets
order by active asc, id asc

meiosis's avatar

What @jaytkay said. Another approach would be

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

klaas4's avatar

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!

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther