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.