Results ordering

Imagine you are responsible for a hotel booking service database. You already know how to select a set of rows that contains information about hotels according to the criteria specified by the client. Still, there are many hotels in the selection, so you need to sort them by price, rating, other attributes, or even expressions requested by the client. Let's see how this can be done in SQL.

ORDER BY

When you query data, SQL does not provide any default order of rows in the query evaluation result. To specify the order of the resulting rows, you should use the ORDER BY clause in the query.

Let's take a look at the example below:

SELECT
    hotel_id, 
    hotel_name, 
    price_per_night,
    price_for_early_check_in,
    rating, 
    stars
FROM 
    hotels
ORDER BY
    price_per_night
;

At the end of the SELECT statement, we have specified that the resulting rows should be sorted by the price_per_night attribute.

You may sort the rows by expressions as well. For example, in the query below, we order hotels by price for two nights with an early check-in:

SELECT
    hotel_id, 
    hotel_name, 
    price_per_night,
    price_for_early_check_in,
    rating, 
    stars
FROM 
    hotels
ORDER BY
    price_per_night*2 + price_for_early_check_in
;

Ascending and descending order

The sorting is based on the definition of the comparison operator (<) for the expression type. It can specify whether greater or smaller values should be placed higher in the list. Let's consider an example:

SELECT
    hotel_id, 
    hotel_name, 
    price_per_night,
    price_for_early_check_in,
    rating, 
    stars
FROM 
    hotels
ORDER BY
    rating DESC
;

Here, the ORDER BY clause is ORDER BY expr [ASC, DESC]; ORDER BY keyword, correct expression, and an optional keyword; either ASC or DESC, specify whether the order is ascending or descending. By default, the order is assumed to be ascending, which is why we omitted the keyword ASC in the previous examples of queries.

Multiple expressions

Let's write a query that sorts hotels by both price and rating:

SELECT
    hotel_id, 
    hotel_name, 
    price_per_night,
    price_for_early_check_in,
    rating, 
    stars
FROM 
    hotels
ORDER BY
    rating DESC,
    price_per_night*2 + price_for_early_check_in 
;

When more than one expression is specified in the ORDER BY clause, the latter values are used to sort rows that are equal according to the former values. Each expression can be followed by an optional ASC or DESC keyword. In our example, hotels should be sorted by rating (from greater to smaller), and those with equal ratings should be sorted by price.

Syntax

If you sort the resulting rows by an expression that defines a result attribute, you can address it in the ORDER BY clause by a column alias or number. For example, in the query below, we sort rows by total price and rating:

SELECT
    hotel_name, 
    price_per_night*2 + price_for_early_check_in AS total_price,
    rating, 
    stars
FROM 
    hotels
ORDER BY
    total_price, rating > 3 DESC
;

Conclusion

The ORDER BY clause allows you to sort the rows returned from the SELECT statement in ascending or descending order based on the specified expressions.

The ORDER BY clause pattern is the following: ORDER BY expr1 [ASC, DESC], ..., exprN [ASC, DESC]; ORDER BY keyword, list of expressions (or corresponding aliases or numbers from the SELECT part of a query) with an optional ASC or DESC keyword.