SQL ORDER BY statement

ORDER BY

In response to a data query, SQL does not provide any default order of rows in the query evaluation result. To specify the order of the resulting rows, use the ORDER BY clause in a query.

Let's take a look at the example below for a hotel booking service database:

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, there is a specification that the resulting rows should be sorted by the price_per_night attribute.

One may sort the rows by expressions as well. For example, in the query below,  hotels are ordered 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 expression type's comparison operator (<). It can specify whether greater or smaller values should be placed higher in the list. 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]; the 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.

Multiple expressions

A query that sorts hotels by both price and rating will look as follows:

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 equal rows according to the former values. Each expression can be followed by an optional ASC or DESC keyword. In the example above, hotels should be sorted by rating (from greater to smaller), and those with equal ratings should be sorted by price.

ORDER BY Syntax

If sorting the resulting rows by an expression that defines a result attribute, address it in the ORDER BY clause by a column alias or number. In the query below, the rows are sorted 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
;

ORDER BY template

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.