Order By Question

[nca]

My MySQL table looks like this:

NAME TYPE
Dog 3
Cat 1
Horse 2
Lamb 0
Sheep 1
Goat 2
Fish 3
Elephant 0

I want them to display in this order:

NAME TYPE
Cat 1
Sheep 1
Horse 2
Goat 2
Dog 3
Fish 3
Elephant 0
Lamb 0

I can get them in somewhat order by doing ORDER BY TYPE ASC but then this puts all of the TYPE 0's at the top when really they need to display below the 1,2,3 TYPES. Is it possible to get them to be in the order I need?

[Derokorian]

(SELECT name,type FROM table WHERE type>0 ORDER BY type ASC)
   UNION
(SELECT name,type FROM table WHERE type=0)

Try that?

[bradgrafelman]

Or just:

SELECT name, type
FROM table
ORDER BY (type=0) ASC, type ASC, name ASC

(untested, not sure if the boolean expression will work as desired - might need to wrap it in an IF() instead).