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
customer_id | customer_name | email_address | last_order_date |
---|---|---|---|
100 | John Doe | john.doe@superpro.com | 09/28/2020 |
101 | Mary Stanton | mary@allinoemarket.com | 09/28/2020 |
102 | Dipali Gupta | dipali.gupta@guptamarket.com | 10/10/2020 |
103 | Peter Can | pcan@canmarkets.com | 05/10/2020 |
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:
customer_name | email_address | last_order_date |
---|---|---|
Dipali Gupta | dipali.gupta@guptamarket.com | 10/10/2020 |
John Doe | john.doe@superpro.com | 09/28/2020 |
Mary Stanton | mary@allinoemarket.com | 09/28/2020 |
Peter Can | pcan@canmarkets.com | 05/10/2020 |
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:
customer_name | email_address | last_order_date |
---|---|---|
Mary Stanton | mary@allinoemarket.com | 09/28/2020 |
Peter Can | pcan@canmarkets.com | 05/10/2020 |
John Doe | john.doe@superpro.com | 09/28/2020 |
Dipali Gupta | dipali.gupta@guptamarket.com | 10/10/2020 |
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:
customer_id | customer_name | email_address | last_order_date |
---|---|---|---|
100 | John Doe | john.doe@superpro.com | 09/28/2020 |
101 | Mary Stanton | mary@allinoemarket.com | 09/28/2020 |
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 First Step: 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:
customer_id | customer_name | email_address | last_order_date |
---|---|---|---|
100 | John Doe | john.doe@superpro.com | 09/28/2020 |
103 | Peter Can | pcan@canmarkets.com | 05/10/2020 |
See you in the next article. Learn SQL, your skills are assets!