MySQL ORDER BY

Data returned from a table by SELECT statement is not in a particular order. Many times you need to have this data in some order and making it suitable for your application needs. For example you may want to display the customer list in sorted order of their names. Another case may be to display merit list of applicants and you want the list of applicants in decreasing order of their final scores. For all such tasks you can add MySQL ORDER BY clause in a SELECT statement to sort data according to your need.

Syntax- MySQL ORDER BY clause in SELECT statement

MySQL ORDER BY clause can be used to sort data in ascending order or descending order on one or more columns of the table. This clause is added after the table name in the SELECT statement. If SELECT statement includes WHERE clause, then ORDER BY will be given after it.

Syntax

SELECT [DISTINCT] *| column-name1 alias1, column-name2 alias2, …..FROM table- name [ORDER BY order-column-name1 [ASC|DESC], order-column-name2[ASC|DESC]…]
  • Keywords – SELECT, DISTINCT, FROM,  ORDER BY
  • *- is used to display all columns of table.
  • Column Name1, Column Name2,… – are table columns to  be displayed in the output of SELECT statement.
  • Table-name is the table used to access data.
  • Aliases are optional and used to give a heading to the column of data being displayed
  • order-column-name [ASC|DESC] are the column names on which data is to be sorted. Multiple column names can be given to sort data on them.

Examples of MySQL ORDER BY Clause

SELECT artist_id, artist_name, country FROM artists ORDER by artist_name;

The data is sorted by artist names in ascending order.

MySQL Order By 
SELECT artist_id, artist_name, country, age FROM artists ORDER by age;

The data is sorted by artist age  in ascending order.

Order by Default
SELECT artist_id, artist_name, country, age FROM artists ORDER by age DESC;

The data is sorted by artist age  in descending order.

Order by Desc
SELECT artist_id, artist_name, country, age FROM artists ORDER by country, age DESC;

The data is sorted by country in ascending order and age in descending order. You will observe that the resulting data is sorted first by country and for each country the data is order in descending order of age. All the artists belonging to USA are seen together after sorting by country and these artists records of USA are sorted in descending order of age.  

Order by multiple columns

Points to Remember

  • Order is ASC by Default. To display table data in descending order DESC keyword is to be mentioned after name of the column.
  • It is not mandatory to include the ORDER BY column names in the columns to be displayed list.
  • Using MySQL ORDER BY clause in select statement will not alter data ordering in the underlying table.
  • If WHERE clause is included in SELECT statement with ORDER BY clause, sorting will happen on data that satisfies WHERE condition.
  • When multiple columns are given in ORDER BY clause, sorting will be from left to right.
  • Different sorting order can be applied to different columns in ORDER BY Clause.