Sorting, Limiting & Selecting the Right Records
In the previous part, you learned how to filter data using WHERE.
Now we'll learn how to control which records appear first, last, or how many records are returned.
These concepts are simple, but they are extremely important for SQL interviews and real-world analytics.
1๏ธโฃ ORDER BY
ORDER BY is used to sort query results.
Syntax
SELECT column1, column2
FROM table_name
ORDER BY column_name;
By default, SQL sorts in ascending order (ASC).
Example:
SELECT
employee_name,
salary
FROM employees
ORDER BY salary;
This displays employees from the lowest salary to the highest.
2๏ธโฃ ASC โ Ascending Order
You can explicitly specify ASC.
SELECT
employee_name,
salary
FROM employees
ORDER BY salary ASC;
For numbers:
100, 250, 500, 1000
For text:
Amit, Neha, Priya, Rahul
3๏ธโฃ DESC โ Descending Order
Use DESC when you want the highest values first.
SELECT
employee_name,
salary
FROM employees
ORDER BY salary DESC;
Result:
Amit: 1200000, Priya: 950000, Rahul: 850000, Neha: 650000
This is one of the most commonly used SQL patterns.
4๏ธโฃ Real-World Example: Top Salaries
Business requirement:
Find the highest-paid employees.
SELECT
employee_name,
salary
FROM employees
ORDER BY salary DESC;
But this might return thousands of employees.
That's where LIMIT becomes useful.
5๏ธโฃ LIMIT
LIMIT restricts the number of rows returned.
SELECT
employee_name,
salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
This returns only the top 5 employees by salary.
Think of it as:
ORDER BY DESC โ Highest first โ LIMIT 5 โ Keep first 5
6๏ธโฃ Top 10 Products by Price
SELECT
product_name,
price
FROM products
ORDER BY price DESC
LIMIT 10;
Very common in analytics.
7๏ธโฃ LIMIT Without ORDER BY
You technically can write:
SELECT *
FROM customers
LIMIT 10;
But this means:
Give me 10 rows.
It does not mean:
Give me the first 10 rows according to some meaningful business order.
Without ORDER BY, the returned order should generally not be relied upon.
If you want the top 10 customers by revenue:
SELECT
customer_id,
revenue
FROM customer_revenue
ORDER BY revenue DESC
LIMIT 10;
8๏ธโฃ OFFSET
OFFSET allows you to skip a number of rows.
Example:
SELECT
employee_name,
salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;
This skips the first 5 rows and returns the next 5.
Conceptually:
Rows 1โ5 โ Skip, Rows 6โ10 โ Return
9๏ธโฃ Pagination
LIMIT and OFFSET are often used for pagination.
For example:
Page 1
SELECT *
FROM customers
ORDER BY customer_id
LIMIT 10 OFFSET 0;
Page 2
SELECT *
FROM customers
ORDER BY customer_id
LIMIT 10 OFFSET 10;
Page 3
SELECT *
FROM customers
ORDER BY customer_id
LIMIT 10 OFFSET 20;
The general pattern is:
Page 1 โ OFFSET 0, Page 2 โ OFFSET 10, Page 3 โ OFFSET 20
๐ Sorting by Multiple Columns
You can sort using more than one column.
Example:
SELECT
employee_name,
department,
salary
FROM employees
ORDER BY department ASC, salary DESC;