ORDER BY clause in SQL
The ORDER BY clause allow us to sort a set of results returned by a SELECT in an ascending or descending manner. The ORDER BY clause will always be written at the end of the statement.
- Descending Order: Records are sorted from highest value to lowest value, with the highest being the first records in the result set.
SELECT *
FROM Customers
ORDER BY customerId DESC;
| customerId | name | lastName | address | bankAccount |
|---|---|---|---|---|
| 5 | Kimberly | Lee | 4298 Drummond Street | 778345112 |
| 4 | Helen | Ward | 711 Hershell Hollow Road | 447824556 |
| 3 | Peter | Davis | 3608 Sycamore Lake Road | 998344567 |
| 2 | Mavin | Pettitt | 2336 Cottonwood Lane | 123456789 |
| 1 | Janice | Haynes | 4822 Radio Park Drive | 111222333 |
- Ascending Order: Records are sorted from lowest value to highest value, with the lowest being the first records in the result set.
SELECT *
FROM Orders
WHERE orderDate > '2022-02-01'
ORDER BY totalPrice ASC;
| orderId | product | productDescription | price | orderDate | productQuantity | totalPrice | customerId |
|---|---|---|---|---|---|---|---|
| 7 | Nintendo Switch | Nintendo Game Console | 299,99 | 2022-09-05 | 3 | 899,97 | 5 |
| 5 | Echo DOT 3 | Alexa Speaker | 39,99 | 2022-09-01 | 50 | 1999,5 | 4 |
| 2 | Play Station 5 | Sony Game Console | 549,95 | 2022-07-18 | 4 | 2199,8 | 2 |
| 4 | MacBook Pro M1 | Apple Notebook | 2449,5 | 2022-06-20 | 1 | 2449,5 | 1 |
| 6 | Echo DOT 4 | Alexa Speaker | 59,99 | 2022-09-01 | 50 | 2999,5 | 4 |
| 1 | Xiaomi Mi 11 | Smartphone | 286,95 | 2022-07-09 | 15 | 4304,25 | 3 |
In this example, it may seem that we are ordering it in descending order if we look at the orderId. But we must remember that we are ordering by the totalPrice,so as we can see the values have been ordered in ascending order, from lowest to highest.
It is also possible to order by column position number, although this can be considered bad programming practice:
SELECT *
FROM Customers
ORDER BY 2 DESC;
This execution will return the records ordered by name in descending order:
| customerId | name | lastName | address | bankAccount |
|---|---|---|---|---|
| 3 | Peter | Davis | 3608 Sycamore Lake Road | 998344567 |
| 2 | Mavin | Pettitt | 2336 Cottonwood Lane | 123456789 |
| 5 | Kimberly | Lee | 4298 Drummond Street | 778345112 |
| 1 | Janice | Haynes | 4822 Radio Park Drive | 111222333 |
| 4 | Helen | Ward | 711 Hershell Hollow Road | 447824556 |
In the case of using ORDER BY without specifying the sort type, the system will perform an ASC (ascending) sort by default.
