๐ Phase 3: SQL for Data Science
๐ Topic 3 โ ORDER BY
ORDER BY is used to sort the rows returned by a SQL query. It helps you arrange data in a meaningful order, such as:
โข Highest salary to lowest salary
โข Lowest price to highest price
โข Newest date to oldest date
โข AโZ or ZโA
โข Highest sales to lowest sales
๐น Basic Syntax
SELECT column1, column2
FROM table_name
ORDER BY column_name;
By default, SQL sorts in ascending order.
SELECT *
FROM employees
ORDER BY salary;
This generally returns employees from the lowest salary to the highest salary.
๐น ASC โ Ascending Order
ASC means ascending.
SELECT *
FROM employees
ORDER BY salary ASC;
For numbers: 10000, 25000, 40000, 75000, 100000
For text: Amazon, Apple, Google, Microsoft
For dates: 2024-01-01, 2024-05-15, 2024-12-20, 2025-03-10
ASC is usually the default, so
ORDER BY salary; is equivalent to ORDER BY salary ASC;๐น DESC โ Descending Order
DESC means descending.
SELECT *
FROM employees
ORDER BY salary DESC;
Example: 100000, 75000, 40000, 25000, 10000
This is especially useful when you want to find:
โข Highest-paid employees
โข Top-selling products
โข Most expensive products
โข Most recent transactions
โข Highest-performing regions
๐น Sorting Text
You can sort text columns alphabetically.
SELECT employee_name, department
FROM employees
ORDER BY employee_name ASC;
SELECT employee_name, department
FROM employees
ORDER BY employee_name DESC;
๐น Sorting Dates
To find the most recent transactions:
SELECT transaction_id, transaction_date, amount
FROM transactions
ORDER BY transaction_date DESC;
To see transactions from oldest to newest:
SELECT transaction_id, transaction_date, amount
FROM transactions
ORDER BY transaction_date ASC;
๐น Sorting by Multiple Columns
This is very important. Suppose you want to sort employees:
1. By department
2. Within each department, by salary from highest to lowest
SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
SQL first sorts by department. When multiple rows have the same department, it then uses salary to determine their order.
Example:
Finance 90000
Finance 70000
Finance 50000
HR 85000
HR 60000
IT 120000
IT 95000
๐น ORDER BY with Calculations
You can also sort using an expression.
SELECT product_name, quantity, price,
quantity * price AS total_value
FROM products
ORDER BY quantity * price DESC;
You can also use the alias in many SQL databases:
SELECT product_name,
quantity * price AS total_value
FROM products
ORDER BY total_value DESC;
๐น ORDER BY with DISTINCT
SELECT DISTINCT department
FROM employees
ORDER BY department ASC;
This returns each department once and sorts them alphabetically.
๐น ORDER BY and NULL Values
NULL represents a missing or unknown value. The position of NULL values when using ORDER BY can vary between SQL databases.
Some databases also support explicit control such as:
ORDER BY salary ASC NULLS LAST;
or
ORDER BY salary DESC NULLS FIRST;
Always check the syntax supported by your SQL database.
๐น ORDER BY with WHERE
WHERE filters the rows first, and ORDER BY sorts the resulting rows.
SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;