SQL ORDER BY and SQL LIMIT

This article covers two new SELECT clauses:

• ORDER BY orders the results of the query.
• LIMIT restricts the result set to a fixed number of rows.

SQL ORDER BY

You can define different orders for each query.

ORDER BY only affects the order the resulting records are returned. It does not affect the results of the query.

Here is the Customer table used in the ORDER BY and LIMIT examples:

Table Customer

You want to obtain a report with the attributes customer_name, email_address, and last_order_date ordered by customer_name. Here is the SQL query:

SELECT   customer_name, email_address, last_order_date 
FROM     customer
ORDER BY customer_name

Here are the results, ordered by customer_name:

You can easily change your order criteria. Here is the same report ordered by the last_order_date attribute:

SELECT   customer_name, email_address, last_order_date 
FROM     customer
ORDER BY last_order_date

The results are:

SQL LIMIT

LIMIT is used to restrict the result set to a fixed number of rows. Naturally if the complete result set has less rows than the fix number we put in the LIMIT, the database returns fewer records than the limit number.

Note: LIMIT is not a standard SQL clause. Other SQL dialects have variations of LIMIT. For example, MS-SQL Server uses TOP, and Oracle uses ROWNUM.

Here is an example, limiting the results to two rows:

SELECT   customer_id, customer_name, email_address 
FROM     customer
LIMIT    2 

Here is the result:

The query can return different results because you are not certain about the order the records are in the results.

For LIMIT to make sense, the results need to be ordered by some criteria. The solution is to combine ORDER BY and LIMIT in the same query.

For example, you want to obtain all the attributes of the customer who placed the most recent order. Here is the SQL query:

SELECT   customer_id, customer_name, email_address, last_order_date
FROM     customer
ORDER BY last_order_date DESC
LIMIT    1

The DESC clause used in ORDER BY. specifies the results in descending order. Combined with the LIMIT 1, the query returns a single record in the result set. In this case, the record with the maximum value in last_order_date.

Closing Example

The final example integrates all the clauses you learned in this article and in SQL SELECT Statement.

Suppose your boss wants a report with the customers who placed the 5 most recent orders during the second quarter. If two or more customers placed anorder on the same day, the report must be ordered by customer name. Here is the query:

SELECT   customer_id, customer_name, email_address, last_order_date
FROM     customer
WHERE    last_order_date >= ‘04/01/2020’ AND last_order_date < ‘07/01/2020’ 
ORDER BY last_order_date DESC, customer_name ASC
LIMIT    5

The two columns specified in ORDER BY. If there is a single record on the last_order_date, the results are in descending order by last_order_date, as specified by DESC. If multiple records have the same value for last_order_date, the results are in ascending order by customer_name, as specified by ASC. Technically, you can leave off the ASC as ascending is the default order. The result is:

See you in the next article. Learn SQL, your skills are assets!