Organising data with the ORDER BY clause

In SQL, the ORDER BY clause is a crucial feature that allows you to sort the result set of a query in a specific order. It’s immensely helpful in organizing data to make it more meaningful and user-friendly.

There are many ways to build SQL competency so we have decided to take a different approach. For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.

Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.

HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.

YOU’VE GOT MAIL!

Good Morning!
It seems we might be missing some context from the restaurant data you shared yesterday.
Kindly provide the list of pre-pandemic restaurants ordered based on the restaurant capacity.
Best Regards,
Mrs Ahmadu (CGO)

SQL TOPIC OF THE DAY

Understanding the ORDER BY Clause

The ORDER BY clause is used in conjunction with the SELECT statement to sort the result set based on one or more columns. This enables you to present the data in a particular order, whether it’s ascending or descending. Here’s the basic syntax of the ORDER BY clause:

SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …;

column1, column2, …: The columns you want to retrieve.
table_name: The name of the table from which you want to fetch data.
[ASC|DESC]: Optional sorting order, where ASC stands for ascending (default) and DESC for descending

Common Use Cases for ORDER BY
The ORDER BY clause is used in various scenarios:

Sorting by a Single Column: You can sort the result set by a single column.

SELECT *
FROM table_name
ORDER BY column1 ASC;

Sorting by Multiple Columns: You can sort by multiple columns, prioritizing one over the other.

SELECT column1, column2, …
FROM table_name
ORDER BY column1 ASC, column2 DESC;

Sorting by Expressions: You can also sort based on expressions. This is useful for calculations or combining multiple columns.

SELECT column1
FROM table_name
ORDER BY (column1 – column2) DESC;

SQL PRACTICE

Here are a few examples to illustrate the use of the ORDER BY clause:

This query will retrieve a list of products sorted by price in descending order:

SELECT product_name, price
FROM products
ORDER BY price DESC;

This will sort employees first by department in ascending order and then by salary in descending order:

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Sort customer orders by order date in ascending order, limiting the result to the first 10 orders:

SELECT *
FROM orders
ORDER BY order_date ASC
LIMIT 10;

Now we can write a script to answer the question of the day.

SELECT name, launch_year, size
FROM restaurants
WHERE launch_year < ‘2019’
ORDER BY size;

FEEDBACK FROM THE CGO

This is definitely insightful.
I might need some time to go through this list so while I do that, kindly provide the list of the top 3 restaurants ordered based on capacity in descending order.
Thanks,
Mrs Ahmadu (CGO)

With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!