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
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".
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;
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
** 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:
Normal Query.
Query: SELECT numbers from table order by numbers
We have as result the following:
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:
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;
Use this:
mysql> select number from (table) order by (number+0);
The (field + 0 ) converts the text/character in the field into an integer.
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.
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');
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.
Here's an example of Eamon Daly's point using the PET table -
9 rows in set (0.00 sec)mysql> select * from pet order by field(species, 'cat', 'dog', 'bird');
You could specify columns in ORDER BY clause, using their position number:
9 rows in set (0.00 sec)In this example:
mysql> SELECT name, owner, birth
-> FROM pet
-> ORDER BY birth;
is equivalent to:
mysql> SELECT name, owner, birth
-> FROM pet
-> ORDER BY 3;
9 rows in set (0.00 sec)
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."
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;
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
Another way of sorting a field containing both text and numbers such as this:
You can query like this:
mysql> select * from table order by length(code), code;
As Linh Hoang say
select * from pet order by field(species, 'cat', 'dog', 'bird') desc;
gives something like this
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;
DEX
In response to Fabrizio Marmonti:
8 rows in set (0.00 sec)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;
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;
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.
A correction to the above:
select * from pet order by field(species, 'cat', 'dog', 'bird') desc, species;
will give the suggested output:
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.
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
...
Add your own comment.