SQL ORDER BY and SQL LIMIT
This article covers two new SELECT clauses:
ORDER BYorders the results of the query.
LIMITrestricts the result set to a fixed number of rows.
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
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
SELECT customer_name, email_address, last_order_date FROM customer ORDER BY last_order_date
The results are:
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.
LIMITis not a standard SQL clause. Other SQL dialects have variations of
LIMIT. For example, MS-SQL Server uses TOP, and Oracle uses
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.
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
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
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!
IN THIS PAGE