DATA with BARAA

SQL ORDER BY

In this tutorial, you will learn how to sort the result set using the SQL ORDER BY clause.

When you use SELECT statement to retrieve data from a table, the rows in result set are not in any particular order. It means that DBMS can return a result set with an unspecified order.

Syntax

Now if you want your result set to be sorted in a particular order.

The basic syntax of the ORDER BY clause to sort the data returned by a query, can be given with:

				
					SELECT column_names
FROM table_name
ORDER BY column_name ASC|DESC
				
			
  • The place of ORDER BY clause is at the end of statement.
  • You have two options to sort your data. You can sort the rows in ascending using ASC Keyword or in descending order using DESC.
  • By default ORDER BY sort the result using ASC, which means if you dont specifiy ASC or DESC the sorting will be done by default in ascending order.

Example

To understand the ORDER BY statement in a better way, let’s look at the following customers table in our tutorial database:

Customers
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin Müller Germany 500
5 Peter Franken USA NULL

Now, let’s check out some examples that demonstrate how it actually works.

Sorting Single Column

We have the following task be to solve using SQL statements

Retrieve all customers where the result is sorted by score (smallest first)

The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.

You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.

				
					SELECT *
FROM customers
ORDER BY score ASC
				
			
				
					SELECT *
FROM customers
ORDER BY score
				
			

After executing the above query, you’ll get the result set something like this:

As you can see the output contains everything the whole customers tables but the rows are sorted by score in ascending order

.
Retrieve all customers where the result is sorted by score (highest first)

Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.

				
					SELECT *
FROM customers
ORDER BY score DESC
				
			

Sorting Multiple Columns

Sometimes you could be in situation where sorting data using one column would not be enough because you might have a lot of duplicate values in your data. Then you can specify more than one column while sorting.

Now, let’s check out some examples that demonstrate how it actually works. let’S say we have the following task:

Retrieve all customers, sorting the result by country (alphabetically) and then by score (highest first)

First we start sorting our data using the first column. The following SQL statement will returns all customers and order the result by the country in ascending order

				
					SELECT *
FROM customers
ORDER BY country ASC
				
			

After executing the above query, you’ll get the result set something like this:

As you can see the result contains only the columns that we specified after SELECT.

				
					SELECT *
FROM customers
ORDER BY country ASC, score DESC
				
			

Notice the change in position of two customers “Martin” and “Maria”.

Once you specify multiple columns after ORDER BY, the Database will sort the result by the first column, then the new ordered list will be sorted again by next column.

				
					SELECT *
FROM customers
ORDER BY country ASC, score DESC
				
			
				
					SELECT *
FROM customers
ORDER BY 1 ASC, 2 DESC
				
			

Sorting by Numbers

It is possible to use the position number of the column instead of using the column name like this:

				
					SELECT *
FROM customers
ORDER BY 1 ASC, 2 DESC
				
			

It is possible to use the position number of the column instead of using the column name like this:

Avoid using numbers in ORDER BY. It is better to use the name of columns

.
Share it !