Sorting SQL row output per an arbitrary order?

Question

So, in my database, I store musical instrument names (and various other attributes). Let's say id is the primary key, and name is a unique key.

In a PHP script, I select items by their instrument class, like so:

$name = mysql_real_escape_string($_POST['name']);
$row = mysql_fetch_row(mysql_query("SELECT * FROM `instruments` WHERE name LIKE '%$name%' ORDER BY id"));

The result table:

id    name
1     "Flute 2"
2     "Bass Flute"
3     "Flute 10"
4     "Flute 7"

This allows me to select the entire family of instruments, like the "Soprano Saxophone", "Alto Saxophone", etc, just by querying "Saxophone".

In that particular example, the results are orered by their id (which you can assume is auto_incremented). More ideal would be ordering by alphabetical order, yes?

id    name
2     "Bass Flute"
3     "Flute 10"
1     "Flute 2"
4     "Flute 7"

That works fine, but being musicians, they like to screw around with the DB admin, and don't take kindly to having "Bass Flutes" listed above "Flute" in the Flute results, and really don't take kindly to having "Flute 10" listed before "Flute 2".

So, seeing as there's no ORDER BY score_order (that would be too easy, wouldn't it...), how could I introduce some sort of ordering system to display instruments properly?

Upon brainstorming, I came upon one solution: I could add one more column of type integer, and "rank" the instruments based on their importance (that is, Piccolos would be "1", Flutes "2", etc):

... nts` WHERE name LIKE '%$name%' ORDER BY rank, name"));

id    name           rank
3     "Flute 10"     2
1     "Flute 2"      2
4     "Flute 7"      2
2     "Bass Flute"   5

However, this doesn't account for the fact that "Flute 10" comes before "Flute 2", alphanumerically.

The ideal result table (ordered by rank, and then by name):

id    name           rank
6     "Piccolo"      1
1     "Flute 2"      2
4     "Flute 7"      2
3     "Flute 10"     2
5     "Alto Flute"   4
2     "Bass Flute"   5

Is there a way to have SQL order records by analysing the entire number, instead of number by number?

Answer 1

You can create a different order doing something like this. It's a hack.

select * from table
order by (
     case name
     when 'Health' then 0 
     when 'Flute 10' then 1
     when 'Freeze' then 2
     when 'Bass Flute' then 3
     end
)

And it's probably a better idea to use the id column.

select * from table
order by (
    case id
    when 3 then 0 
    when 1 then 1
    when 4 then 2
    when 2 then 3
    end
)