The Order by clause

A few main points about the SQL order by clause:

• The Order by clause is used with the SQL Select statement to sort the results in ascending or descending order.
• You have to specify one or more columns for what you want to sort the table result set.
• The default SQL sort is ascending order, i.e. if you don’t specify it will return the result in ascending order.
• Alternatively you can use ASC keyword to sort results in ascending order in the order by clause.
• To sort results in descending order use the DESC keyword in the order by SQL.
• The example can be sorting result on the basis of the alphabetical order for employee names table.
• Or sorting results order by date for customers’ orders table.

Example of order by ASC

See an example of order by desc

Syntax of SQL Order by clause

The general syntax of Order by clause is:

Select * from table_name

Where col1=val1, col2=val2

Order by some_column_in_table

Examples of using Order by – SQL clause

Let us show you examples of order by clause with our created database and table. We will use the test_db database and tbl_employee table, that we created in the Create table chapter, to show a few examples of SQL order by clause.

Just to remind this is how our example table is created in the test_db:

We also have a few records in that table to work with the order by clause.

Example of order by in ASC order for single column

This example will return all records from the tbl_employee table and is sorted by employee names. i.e. A first and Z at the last on the emp_name column.

The order by ASC query will be:

See order by example online

Example of order by in DESC order for single column

This example will return all records from the tbl_employee table and is sorted by the employee names in descending order i.e. name started with the letter ‘Z’ will come first and ‘A’ at the last on emp_name column.

The order by DESC query:

See order by desc example online

Example of select – order by with multiple columns

You may use multiple column names to sort the results by using order by in SQL. Just separate each column by a comma. Also, note that the order of columns is important in the order by clause. For example, if you want to sort out your email inbox by email_address and then receiving date. It will first sort email_addresses and let us say you have got many emails from abc@test.com, it will then sort out on the basis of date and then move to next email.

For our table, tbl_employee, let us use the SQL sort by the joining_date and emp_name.

The order by query is:

See order by example online

The graphic shows two queries: with a simple select statement and then SQL select with the order by joining_date and emp_name.