MySQL Order By Clause


MySQL ORDER BY clause is used to display records in ascending/descending order on the screen. Order by clause is used with the SELECT statement.
Syntax

SELECT column1, column2,.................
[ where < condition > ]
ORDER BY column1 ASC/DESC

NOTE : ASC is optional to display records in ascending order but DESC is compulsory to display records in descending order.

Example Database for Demonstration of ORDER BY clause

mysql> select * from student;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 0000000004  | F      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

Example Queries to display records

Suppose you want to display all the records in the ascending order of their name.

mysql> select * from student
    -> order by name;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 101   | rakesh  | sci    | 96.56 | A     | 0000000004  | F      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

IN descending order- The query will be like this

mysql> select * from student
    -> order by name DESC;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 101   | rakesh  | sci    | 96.56 | A     | 0000000004  | F      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

When you have duplicate values in your target column then select command display values in the order they were feed. We can overcome this using the second column.

mysql> select * from student
    -> order by name ASC, stream DESC;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 101   | rakesh  | sci    | 96.56 | A     | 0000000004  | F      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

When duplicate entries will be encountered then the second column will play its role to place the record.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.