[RESOLVED] Order in SQL, numerically ASC but with 0 last

[Piranha]

I have a table with the basic structure:

CREATE TABLE names (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    active INT
)

Active is set to 0 for inactive, 1 for primary and 2 and upwards for secondary results. When I run a query, how can I first get every post with active = 1, then active = 2 and so on all and last active = 0?

If I would use only a few numbers I could do it like this:

SELECT id, name, active FROM names ORDER BY FIELD(active, 1, 2, 0)

But since I don't know how big the largest number in active will be, and since I don't want to add all numbers in the FIELD I don't really know how to handle this.

[bradgrafelman]

Perhaps you need either two separate queries or a union of two queries? Namely:

SELECT id, name, active FROM names WHERE active > 0 ORDER BY active ASC

and:

SELECT id, name, active FROM names WHERE active = 0

(in that order, obviously).

[Lars_Berg]

order by case active when 0 then 1 else 0 end, active

[bogu]

Lars Berg wrote:

order by case active when 0 then 1 else 0 end, active

Nice query, I had to try it ... 🙂

[Piranha]

Tack så mycket Lars 😃

I would never had figured that out myself, I would probably do as bradgrafelman suggested, but now I know there are alternatives 😃