How can I make MySQL sort data using a custom sorting method?

Question

I have

SELECT revision 
FROM   table 
WHERE  id = 4 
ORDER  BY revision DESC 
LIMIT  1; 

Ignoring the limit clause for a moment, here are the results:

Returns   |   I want
--------------------
   Z      |    AD   //with LIMIT clause this is what's returned 
                    //& what I seek is the first row
                    //provided it's sorted properly
   Y      |    AC       
   X      |    AB 
   W      |    AA 
   ....   |    Z    
   B      |    Y    
   A      |    X    
   AD     |    ....     
   AC     |    C    
   AB     |    B    
   AA     |    A

Can this be done with MySQL?

My goal is to have the data sorted as above. Data enters PHP, where I can re-sort it, but I was curious if I could do this directly from MySQL.

Answer 1

Sort by character length desc, then alphabet.

SELECT revision 
FROM   table 
WHERE  id = 4 
ORDER  BY CHAR_LENGTH(revision) DESC, 
          revision DESC 
LIMIT  1;