ORDER BY (the lesser known way)

We all know with MySQL you can use ORDER BY with a list of columns to return an ordered set, e.g. ORDER BY name, type, state;
I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.

However I needed to do some selective ordering of a type field, I didn’t want to for example have a lookup table just to join for ordering. While contemplating a means of achieving this, I asked a work colleague, who I figured may have just experienced this problem before. Lone behold I became the student as I discovered there is a third syntax with ORDER BY, using expressions.

mysql> create table test(name varchar(10) not null, type varchar(10) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test(name,type) values
('Apples','Fruit'),
('Bananas','Fruit'),
('Carrots','Veg'),
('Onions','Veg'),
('Beer','Liquid'),
('Water','Liquid'),
('Crackers','Food');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select name from test
order by type='Veg' DESC,
         type='Fruit' DESC,
         type='Food' DESC,
         type='Liquid' DESC;
+----------+
| name     |
+----------+
| Carrots  |
| Onions   |
| Apples   |
| Bananas  |
| Crackers |
| Beer     |
| Water    |
+----------+
7 rows in set (0.00 sec)

Of course, reading the MySQL Manual confirms this on the SELECT command.
I’ve not read the MySQL manual from cover to cover, since 4.x days. Perhaps it’s time.

Thanks to Nick Pisarro of Blog Revolution for this most valuable tip.

15 Responses to “ORDER BY (the lesser known way)”

  1. Josh Davis says:

    Talking about expressions in ORDER BY, MySQL has a function that almost seems to exist for that very purpose, FIELD(). For instance, this query should return the same result as your example:

    select name from test
    order by FIELD(type, ‘Veg’, ‘Fruit’, ‘Food’, ‘Liquid’);

    http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_field

  2. Rob Wultsch says:

    I suggest looking at using the field syntax:

    mysql> SELECT name
    -> FROM test
    -> ORDER BY FIELD(type,’Veg’,'Fruit’,'Food’,'Liquid’);
    +———-+
    | name |
    +———-+
    | Carrots |
    | Onions |
    | Apples |
    | Bananas |
    | Crackers |
    | Beer |
    | Water |
    +———-+
    7 rows in set (0.00 sec)

  3. Joe Izenman says:

    It’s one of those things that I never would have thought of, but makes perfect sense and seems remarkably obvious now that I see it. After all, each of those expressions evaluates to a 1 or a 0, at which point it returns those that evaulate to 1 (true) first (when ordering descending).

  4. Bill Karwin says:

    Ronald, if you need a break from reading the MySQL manual, please also read this:
    http://www.phrases.org.uk/meanings/lo-and-behold.html

  5. Sheeri says:

    Yeah, another neat syntax is

    INSERT INTO tbl1 SET fld1=val1, SET fld2=val2, ….

    which is much better than INSERT INTO tbl1 (fld1, fld2…) VALUES (val1, val2…) when there are lots of fields.

  6. Remi says:

    > I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.

    I’m really happy that a lot of people don’t even know about it.
    I think this is the worse syntax for the ORDER BY clause, especially for maintainability.

    What happen during an upgrade from 4.x to 5.x where column order in a result set can change ? (the JOIN key appear twice in 4.x, once in 5.x)

    ++

  7. Whether the original syntax or FIELD is used I think it is even more interesting with GROUP BY!

    select
    count(type) as ‘number of products’,
    if(type=’Liquid’,'what you drink’,'everything else .. and less important’) as ‘what is this for’
    from test
    group by field(type,’Liquid’) desc;

  8. Nick Pisarro says:

    The field() alternative as others have pointed out does look promising as well.

  9. Arjen Lentz says:

    I teach students to recognise the ordinal position syntax (ORDER BY 1,2) but teach them to get rid of it.
    I presume you picked up this syntax at Oracle, where you can’t have expressions or calculated fields in the ORDER BY.
    Thus, SELECT foo * 2 AS bar FROM …. ORDER BY bar would not be allowed. So you use ORDER BY 1.
    But it IS allowed in MySQL and then, it (IMHO) becomes sensible to get rid of the ordinal position stuff, as it keeps queries more maintainable and less error-prone. Otherwise, if someone changes the SELECT columns in some way, the ORDER BY can break without people realising.

  10. Rob G says:

    That is nifty. My ungainly equivalent result using Oracle is

    select name from
    (
    SELECT name,
    (case
    when type=’Veg’ then 1
    when type=’Fruit’ then 2
    when type=’Food’ then 3
    when type=’Liquid’ then 4
    else 5 end) sort_order
    FROM TEST
    )
    order by sort_order, name
    /

  11. Rob G says:

    I like that field() function.

    Here is another go from Oracle. Just working through this – do not mean to pollute. I found your blog as part of trying to learn about ec2.

    SELECT name
    FROM TEST
    order by (case
    when type=’Veg’ then 1
    when type=’Fruit’ then 2
    when type=’Food’ then 3
    when type=’Liquid’ then 4
    else 5 end), name
    /

  12. David says:

    “Low and behold”

  13. David says:

    Oops, finger autocompletion:

    “Lo and behold”

  14. Pete says:

    You can also use aggregate functions in ORDER BY, such as

    select name from test order by length(name)

  15. RaBe says:

    Hi,

    Thanks for this Guide! I think it will help me out a lot.
    But just while watching over this example (havnt tried it out myself yet) i think you may have a small mistake in your result set because your ordering by DESC and your results are listed in ASC order.

    best regards
    RB