department
Then within each department:
salary DESC
Example:
Finance: 950000, Finance: 750000, IT: 1200000, IT: 850000, IT: 700000
1️⃣1️⃣ Why Multiple Sorting Columns Matter
Suppose several products have the same price.
Laptop: 50000, Phone: 50000, Tablet: 50000
You can add a second sorting condition:
SELECT
product_name,
price
FROM products
ORDER BY
price DESC,
product_name ASC;
Now SQL uses the product name to break ties.
1️⃣2️⃣ Sorting by Calculated Values
You can sort using an expression.
Example:
SELECT
product_name,
selling_price,
cost_price,
selling_price - cost_price AS profit
FROM products
ORDER BY profit DESC;
This displays the products with the highest calculated profit first.
1️⃣3️⃣ Sorting by an Alias
You can usually sort using a column alias defined in the SELECT list.
SELECT
product_name,
selling_price - cost_price AS profit
FROM products
ORDER BY profit DESC;
This is convenient and makes the query easier to read.
1️⃣4️⃣ Sorting by Column Position
Some SQL dialects allow:
SELECT
product_name,
price
FROM products
ORDER BY 2 DESC;
Here:
1 → product_name, 2 → price
So SQL sorts by the second selected column.
⚠️ Best Practice
Although positional ordering may be supported, prefer:
ORDER BY price DESC;
because it is easier to understand and less fragile if the SELECT list changes.
1️⃣5️⃣ NULL Values and ORDER BY
NULL values require special attention.
For example:
Rahul: 5000, Priya: NULL, Amit: 8000
The position of NULL values when sorting can vary by database system and sort direction.
Some systems allow explicit control:
ORDER BY bonus DESC NULLS LAST;
or:
ORDER BY bonus ASC NULLS FIRST;
Interview Tip
Don't assume NULL sorting behavior is identical across MySQL, PostgreSQL, SQL Server, and Oracle.
1️⃣6️⃣ ORDER BY With WHERE
You can combine filtering and sorting.
Example:
Find Mumbai customers and display the highest spenders first.
SELECT
customer_name,
city,
total_spend
FROM customers
WHERE city = 'Mumbai'
ORDER BY total_spend DESC;
Execution conceptually works as:
FROM → WHERE → SELECT → ORDER BY
The detailed logical processing order has a few nuances, but this is a useful beginner mental model.
1️⃣7️⃣ ORDER BY With LIMIT
This combination is extremely important.
Requirement:
Find the top 3 customers by spending.
SELECT
customer_name,
total_spend
FROM customers
ORDER BY total_spend DESC
LIMIT 3;
This pattern appears constantly in SQL interviews.
1️⃣8️⃣ Top N Per Category
Here's an important distinction.
Suppose you need:
Top 3 products overall.
You can use:
ORDER BY revenue DESC LIMIT 3;
But if the requirement is:
Top 3 products in every category
LIMIT 3 alone isn't enough.
You'll eventually need window functions such as ROW_NUMBER() or DENSE_RANK().
Example:
WITH ranked_products AS (
SELECT
product_name,
category,
revenue,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY revenue DESC
) AS rn
FROM product_sales
)
SELECT
product_name,
category,
revenue
FROM ranked_products
WHERE rn <= 3;
Don't worry if this looks advanced.
You'll learn window functions later.
1️⃣9️⃣ DISTINCT & ORDER BY
You can combine DISTINCT and ORDER BY.
Example:
SELECT DISTINCT city
FROM customers
ORDER BY city ASC;
