Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


3.3.4.4 行のソート

前の例で、結果の行の表示には特定の順序がないことに気付いたでしょう。多くの場合、クエリーの出力は、行を何らかの意味のある順序でソートすると確認しやすくなります。結果をソートするには、ORDER BY 句を使用します。

次に、ペットの生年月日を日付でソートしたものを示します。

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 |
+----------+------------+

文字型のカラムでは、ソートはほかのすべての比較演算と同様に、通常大文字小文字の区別なしで実行されます。したがって、大文字と小文字の違いしかないカラムの場合、順序は未定義になります。カラムのソートで大文字と小文字を区別するには、BINARY を使用し、ORDER BY BINARY col_name のように指定します。

デフォルトのソート順序は昇順で、最小値が最初になります。逆順 (降順) でソートするには、ソートするカラムの名前に DESC キーワードを加えてください。

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 |
+----------+------------+

複数のカラムでソートでき、ソートの方向はカラムごとに変えることができます。たとえば、ペットの種類で昇順にソートしてから、同じ種類の中では生年月日で降順に (若い順に) ソートするには、次のクエリーを使用します。

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 |
+----------+---------+------------+

DESC キーワードはその直前のカラム名 (birth) だけに適用されます。species カラムのソート順序には影響を与えません。


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.