Bangalore, Delhi, Hyderabad, Mumbai, Pune
2️⃣0️⃣ ORDER BY Multiple Columns With Different Directions
You can specify different directions.
SELECT
department,
employee_name,
salary
FROM employees
ORDER BY
department ASC,
salary DESC;
Meaning:
Department → A to Z, Salary → Highest to Lowest within department
2️⃣1️⃣ Real-World Business Example
Requirement:
Show the 5 most expensive products that are currently active.
SELECT
product_name,
category,
price
FROM products
WHERE product_status = 'Active'
ORDER BY price DESC
LIMIT 5;
Notice the combination:
WHERE → Filter active products, ORDER BY → Highest price first, LIMIT → Keep only 5
2️⃣2️⃣ Another Example
Requirement:
Find the 10 customers with the highest total spending.
SELECT
customer_id,
customer_name,
total_spend
FROM customers
ORDER BY total_spend DESC
LIMIT 10;
This is a classic Data Analyst query.
🧠 Common Beginner Mistakes
❌ Mistake 1: Forgetting DESC
If you want the highest values first:
ORDER BY salary DESC;
Not:
ORDER BY salary;
because the default is typically ascending.
❌ Mistake 2: Using LIMIT without ORDER BY
This:
SELECT *
FROM products
LIMIT 5;
doesn't reliably identify the "top 5" by any business metric.
Instead:
SELECT *
FROM products
ORDER BY revenue DESC
LIMIT 5;
❌ Mistake 3: Confusing LIMIT with filtering
LIMIT doesn't filter rows based on a condition.
LIMIT 10 means:
Return at most 10 rows.
Whereas:
WHERE salary > 800000 means:
Return rows satisfying a condition.
❌ Mistake 4: Using LIMIT for Top N per Group
ORDER BY revenue DESC LIMIT 3; returns 3 rows overall.
It does not return 3 rows from every category.
💼 SQL Interview Questions
Q1. What is ORDER BY?
Answer: ORDER BY sorts the result set according to one or more columns or expressions.
Q2. What is the default sorting direction?
Answer: Ascending (ASC) is the default in standard SQL usage.
Q3. How do you find the highest-paid employee?
SELECT employee_name, salary FROM employees ORDER BY salary DESC LIMIT 1;
Q4. How do you find the top 5 products by revenue?
SELECT product_name, revenue FROM products ORDER BY revenue DESC LIMIT 5;
Q5. What does OFFSET do?
Answer: OFFSET skips a specified number of rows before returning the remaining rows subject to LIMIT or the database's equivalent pagination mechanism.
Q6. Can you sort by multiple columns?
Answer: Yes. ORDER BY department, salary DESC;
Q7. Can you use an alias in ORDER BY?
Answer: In most common SQL systems, yes.
SELECT salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC;