Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.7Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

4.3.4.4 Sorting Rows

You may have noticed in the preceding examples that the result rows are displayed in no particular order. It is often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause.

Here are animal birthdays, sorted by date:

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.

The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

You can sort on multiple columns, and you can sort different columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

The DESC keyword applies only to the column name immediately preceding it (birth); it does not affect the species column sort order.


User Comments
  Posted by on January 29, 2002
For an example of sorting based on a dynamically
generated column, see the example of sorting
based on age in section 3.3.4.5 "Date
Calculations".
  Posted by Billy Kimble on June 2, 2003
If you want to explicity specify the order of which 'order by' comes back in, like if you had a priority field that had the values "Low" "High" or "Medium" .. do this:

select * from tablename order by priority='High' DESC, priority='Medium' DESC, priority='Low" DESC;

  Posted by noatun on November 3, 2003
Sometimes you might want to sort names. If you have First and Last names in one field, seperated by a blank, you can do this by:
SELECT * FROM my_addressbook ORDER BY SUBSTRING_INDEX(name, ' ', -1) ASC

This works with John Adam, John F. Adam
but not with John F.Adam
  Posted by Juan Ignacio Gomez on March 23, 2004
** Order By number Like this Number was a Text **
Some times you need to order by a column that contains numbers, but as
if it would be text, example:

Field Name: Numbers
Type: Integer(11)
Data:
+----------+
| numbers |
+----------+
| 10 |
| 500 |
| 1 |
| 3000 |
| 20 |
| 50 |
| 30 |
| 1000 |
+----------+

Normal Query.
Query: SELECT numbers from table order by numbers
We have as result the following:
+----------+
| numbers |
+----------+
| 1 |
| 10 |
| 20 |
| 30 |
| 50 |
| 500 |
| 1000 |
| 3000 |
+----------+

We must make a Query, turning the numeric(integer) field to text and
ordered at this last one, Example:
Query: SELECT left(numbers, 11) as numbersSTR from table order by numbersSTR
We have as result the following:
+------------+
| numbersSTR |
+------------+
| 1 |
| 10 |
| 1000 |
| 20 |
| 30 |
| 3000 |
| 50 |
| 500 |
+------------+
| Unitec - Venezuela
  Posted by Albert Vega on September 3, 2004
Similar to the above, if you have numbers written in text or characters, and you want to sort them in ascending order and you don't want this:
mysql> select number from (table) order by number;
+--------+
| number |
+--------+
| 1 |
| 10 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+--------+

Use this:
mysql> select number from (table) order by (number+0);
+--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+--------+
The (field + 0 ) converts the text/character in the field into an integer.

  Posted by steve cooley on December 23, 2004
You should be able to do this:

SELECT * from table order by primary desc

but you can't. You have to know the name of the primary key if you want to sort rows by it.
  Posted by Eamon Daly on March 28, 2005
A common question on the mailing lists is how to sort results in a particular order. Just use the FIELD function:

SELECT * FROM tickets
ORDER BY FIELD(priority, 'High', 'Normal', 'Low', 'The Abyss');

  Posted by Frank Farmer on May 23, 2005
Re: Albert Vega on September 3 2004 11:17pm

When ordering strings as numbers, ORDER BY (number+0) produces the correct results; however it is very slow!

I have a 70,000 row table on which a query with ORDER BY (number) runs in 0.0052 seconds. With ORDER BY (number+0) the exact same query takes 3.5 seconds!

Clearly, this method is unacceptable for large (or even medium) tables.
  Posted by Edward Lipchus on October 11, 2006
Here's an example of Eamon Daly's point using the PET table -

mysql> select * from pet order by field(species, 'cat', 'dog', 'bird');
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | N | 1997-12-09 | NULL |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

  Posted by Fabrizio Marmonti on November 13, 2006
You could specify columns in ORDER BY clause, using their position number:

In this example:

mysql> SELECT name, owner, birth
-> FROM pet
-> ORDER BY birth;
+-----------+---------+------------+
| name | owner | birth |
+-----------+---------+------------+
| Buffy | Harold | 1989-05-13 |
| Bowser | Diane | 1989-08-31 |
| Fang | Benny | 1990-08-27 |
| Fluffy | Harold | 1993-02-04 |
| Claws | Gwen | 1994-03-17 |
| Slim | Benny | 1996-04-29 |
| Whistler | Gwen | 1997-12-09 |
| Chirpy | Gwen | 1998-09-11 |
| Puffball | Diane | 1999-03-30 |
+-----------+---------+------------+
9 rows in set (0.00 sec)

is equivalent to:

mysql> SELECT name, owner, birth
-> FROM pet
-> ORDER BY 3;
+-----------+---------+------------+
| name | owner | birth |
+-----------+---------+------------+
| Buffy | Harold | 1989-05-13 |
| Bowser | Diane | 1989-08-31 |
| Fang | Benny | 1990-08-27 |
| Fluffy | Harold | 1993-02-04 |
| Claws | Gwen | 1994-03-17 |
| Slim | Benny | 1996-04-29 |
| Whistler | Gwen | 1997-12-09 |
| Chirpy | Gwen | 1998-09-11 |
| Puffball | Diane | 1999-03-30 |
+-----------+---------+------------+
9 rows in set (0.00 sec)

  Posted by Brian Papantonio on January 1, 2007
In response to Steve C.:

The correct syntax to do that would be:

SELECT * FROM tbl_name ORDER BY _rowid DESC

The Create Table manual states:

"If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements."
  Posted by Linh Hoang on February 11, 2007
Follow up with Edward Lipchus's explanation:

field(para1, para2, para3, para4 .....)

paramenter 1 is the column's name. Parameter 2 and onward are the ranking you would like. So 'cat' would be the highest rank and would be listed before 'dog'. Other unspecified ranks, however, would be appeared before everything else, like 'snake'. If you want unspecified rank to be displayed last, just do an inverse of the field's ranking parameters.

select * from pet order by field(species, 'bird', 'dog', 'cat') desc;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birthday | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+

  Posted by Terry Makris on April 8, 2007
If you find you are getting Syntax errors, don't forget the rule of 'reserved laws'.

"SELECT * FROM table ORDER BY column DESC";

should be:

"SELECT * FROM `table` ORDER BY `column` DESC";

Just a little tip for all you sorter's :p
  Posted by Ikhwan Nordin on May 31, 2007
Another way of sorting a field containing both text and numbers such as this:

+------+
| code |
+------+
| A10 |
| A20 |
| A5 |
| A6 |
| A7 |
| A8 |
| A9 |
+------+

You can query like this:
mysql> select * from table order by length(code), code;

+------+
| code |
+------+
| A5 |
| A6 |
| A7 |
| A8 |
| A9 |
| A10 |
| A20 |
+------+
  Posted by Roberto Eduardo Decurnex Gorosito on June 19, 2007
As Linh Hoang say

select * from pet order by field(species, 'cat', 'dog', 'bird') desc;

gives something like this

+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birthday | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+

if you want to order the unspecified ranks just type something like this:

select * from pet order by species, field(species, 'cat', 'dog', 'bird') desc;

+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birthday | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| PusffBall| Diane | hamster | f | 1999-03-30 | NULL |
| Slim | Denny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+

DEX
  Posted by Dionysis Zindros on May 24, 2008
In response to Fabrizio Marmonti:

Be careful when using column position numbers when sorting. You can use column position numbers only when they are used directly as literal numerals:

mysql> SELECT `user_id`, `user_name` FROM `users` ORDER BY 2;
+---------+------------+
| user_id | user_name |
+---------+------------+
| 42 | abresas |
| 1 | dionyziz |
| 36 | dionyziz2 |
| 2 | izual |
| 8 | kokos |
| 37 | kostis90gr |
| 9 | test |
| 35 | usertest |
+---------+------------+
8 rows in set (0.00 sec)

Notice how in the following example, you cannot use any kind of processing to determine the target position:

mysql> SELECT `user_id`, `user_name` FROM `users` ORDER BY 4 - 2;
+---------+------------+
| user_id | user_name |
+---------+------------+
| 1 | dionyziz |
| 2 | izual |
| 8 | kokos |
| 36 | dionyziz2 |
| 9 | test |
| 35 | usertest |
| 37 | kostis90gr |
| 42 | abresas |
+---------+------------+
8 rows in set (0.00 sec)

In the latter example, MySQL sorts by the actual numeric value, which is "2" for every record, and hence the default order remains unchanged.

  Posted by Duck Williamson on June 13, 2008
A correction to the above:

select * from pet order by field(species, 'cat', 'dog', 'bird') desc, species;

will give the suggested output:

+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birthday | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| PusffBall| Diane | hamster | f | 1999-03-30 | NULL |
| Slim | Denny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
  Posted by Amir Wald on October 10, 2008
Please mind the methods described here for sorting a mixture of numbers and letters are helpful only in some case. The following case would be harder to sort:
1,2,10,x1,x2,x2a,x10,yy1,yy1a,yy2,yy10

My guess is that you'll need a not so simple function (or expression) to sort the letter prefix, then the numbers inside that prefix and then the suffix. If you've already done something of the like, feel free to post it here.
  Posted by Ari El on November 19, 2009
This solved a problem with "natural" sort:

SELECT * FROM table_1 ORDER BY CAST(mid(c1, 6, LENGTH(c1) -5) AS unsigned);

change "table_1" and "c1" to your fields, respectively.

We got:

1
2
3
T3
T4
5
6
...

  Posted by Bill CreateSource on May 29, 2010
An easy method of allowing user-selectable ordering of items is with an integer int/tinyint field:

sequence tinyint(3) default 0

The default behavior will always put zero's first. Some users may find this confusing, "I set it to 1 and it should be number 1." In these instances you can do this:

Which will put the zero's below.

A full test for you:

create table test2 (id int(11) primary key auto_increment, title varchar (255), sequence tinyint(3) default 0);

insert into test2 (title,sequence) values ('a row1',0);
insert into test2 (title,sequence) values ('d row2',0);
insert into test2 (title,sequence) values ('w row3',0);
insert into test2 (title,sequence) values ('h row4',0);
insert into test2 (title,sequence) values ('r row5',0);
insert into test2 (title,sequence) values ('k row6',1);
insert into test2 (title,sequence) values ('m row7',2);
insert into test2 (title,sequence) values ('a row8',3);

select * from test2 order by sequence>0 desc, sequence asc, title asc;

Note how the secondary order of title arranges items alphabetically if no sequence is assigned.

  Posted by gaurav kaushik on November 10, 2010
hey Mr. duck and Mr. roberto what u ppl are saying....if u r giving this command :
select * from pet order by field(species, 'bird', 'dog', 'cat') desc;
then cat will be having the lowest priority will appear first....in ascending order bird will appear first

  Posted by mustafa akcakaya on January 5, 2011
I found this:

Let's say you have "name" field and it contains mixed data like this:

1
6a
3s
aa
bb
ca
..

and you want to sort them like (letters first, after numbers)

aa
bb
ca
1
3s
6a

SELECT name FROM table ORDER BY FIELD(name,name) ASC

If any other way exists, tell me!
  Posted by Ilan Hazan on March 22, 2011
In a case that you copy (INSERT INTO .. SELECT ..) a MyISAM table that have a PRIMARY key, it is much faster to insert the new rows in the primary key order.
See interesting research: http://www.mysqldiary.com/if-you-copy-a-myisam-table-with-primary-key-don%E2%80%99t-forget-to-order-the-rows-first/

  Posted by Василий Тарковский on November 22, 2014
To sort by type I use:
select * from ad order by field(type, 'free', 'marked, urgently, top') asc, create_time desc;
or
select * from ad order by field(type, 'free, urgently', 'marked, top') asc, create_time desc;
Sign Up Login You must be logged in to post a comment.