October 1, 2026

MySQL ORDER BY, LIMIT & DISTINCT: Sorting and Managing Query Results

Introduction

When working with MySQL databases, we often need to control how query results are displayed.

For example:

• Show products from lowest price to highest price

• Show students alphabetically by name

• Display the latest 10 customers

• Show only the first 5 products

• Display unique cities without duplicate values

MySQL provides three useful clauses for these requirements:

• ORDER BY — Sort query results

• LIMIT — Restrict the number of records returned

• DISTINCT — Remove duplicate values from query results

In this tutorial, we will learn these three concepts with practical and real-world examples.

---

1. What is ORDER BY in MySQL?

The ORDER BY clause is used to sort records in a specific order.

You can sort data:

• In ascending order

• In descending order

Basic Syntax

SELECT *
FROM table_name
ORDER BY column_name;

By default, MySQL sorts the results in ascending order (ASC).

---

2. ORDER BY ASC

ASC means Ascending.

For numbers:

10
20
30
40
50

For names:

Amit
Neha
Priya
Rahul

Example

Suppose we have a students table:

id	name	age	city
1	Rahul	22	Raipur
2	Amit	18	Bilaspur
3	Priya	20	Durg
4	Neha	19	Raipur

Sort students by name:

SELECT *
FROM students
ORDER BY name ASC;

Result

id	name	age	city
2	Amit	18	Bilaspur
4	Neha	19	Raipur
3	Priya	20	Durg
1	Rahul	22	Raipur

---

3. ORDER BY DESC

DESC means Descending.

For numbers:

50
40
30
20
10

For names:

Rahul
Priya
Neha
Amit

Example

Sort students from oldest to youngest:

SELECT *
FROM students
ORDER BY age DESC;

Result

id	name	age
1	Rahul	22
3	Priya	20
4	Neha	19
2	Amit	18

---

4. Sorting Products by Price

Suppose we have an e-commerce products table:

id	name	price	stock
1	Laptop	55000	10
2	Mobile	25000	20
3	Keyboard	1500	50
4	Monitor	12000	15

To show the cheapest products first:

SELECT *
FROM products
ORDER BY price ASC;

To show the most expensive products first:

SELECT *
FROM products
ORDER BY price DESC;

This is commonly used on e-commerce websites.

---

5. ORDER BY with WHERE

ORDER BY can be combined with WHERE.

For example, find products from the Mobile category and show the most expensive first:

SELECT *
FROM products
WHERE category = 'Mobile'
ORDER BY price DESC;

Here MySQL first filters the records using WHERE and then sorts the results using ORDER BY.

---

6. Sorting by Multiple Columns

You can sort by more than one column.

Example

Suppose we want:

1. Students sorted by city

2. Students within each city sorted by name

Query:

SELECT *
FROM students
ORDER BY city ASC, name ASC;

MySQL first sorts by city.

If multiple students have the same city, it then sorts those students by name.

---

7. Multiple Column Sorting Example

Suppose the data is:

name	city	age
Rahul	Raipur	22
Amit	Raipur	18
Priya	Durg	20
Neha	Durg	19

Query:

SELECT *
FROM students
ORDER BY city ASC, age DESC;

The result will first be grouped alphabetically by city, and within each city, students will be sorted by age from highest to lowest.

---

8. What is LIMIT in MySQL?

The LIMIT clause is used to restrict the number of records returned by a query.

Basic Syntax

SELECT *
FROM table_name
LIMIT number;

Example

Display only 5 students:

SELECT *
FROM students
LIMIT 5;

Even if the table contains 1,000 students, MySQL returns only 5 records.

---

9. LIMIT with ORDER BY

LIMIT becomes especially useful when combined with ORDER BY.

Suppose an e-commerce website wants to display the 5 cheapest products:

SELECT *
FROM products
ORDER BY price ASC
LIMIT 5;

The query:

1. Sorts products by price

2. Places the cheapest products first

3. Returns only 5 records

---

10. Find the Most Expensive Products

To display the 5 most expensive products:

SELECT *
FROM products
ORDER BY price DESC
LIMIT 5;

This is useful for product listing pages and reports.

---

11. LIMIT with WHERE

You can also use WHERE, ORDER BY, and LIMIT together.

Example:

SELECT *
FROM products
WHERE category = 'Laptop'
ORDER BY price DESC
LIMIT 5;

This means:

Find laptops, sort them from highest price to lowest price, and show only 5 products.

---

12. LIMIT with OFFSET

MySQL also allows you to skip a certain number of records before returning results.

Syntax

SELECT *
FROM table_name
LIMIT offset, number;

For example:

SELECT *
FROM products
LIMIT 10, 5;

This means:

Skip the first 10 records and return the next 5 records.

---

13. Real-Time Pagination Example

Pagination is very common on websites.

Suppose a product page displays 10 products per page.

Page 1

SELECT *
FROM products
LIMIT 0, 10;

Page 2

SELECT *
FROM products
LIMIT 10, 10;

Page 3

SELECT *
FROM products
LIMIT 20, 10;

The pattern is:

Page 1 → LIMIT 0, 10
Page 2 → LIMIT 10, 10
Page 3 → LIMIT 20, 10
Page 4 → LIMIT 30, 10

This is one of the most common uses of LIMIT in PHP and CodeIgniter applications.

---

14. What is DISTINCT in MySQL?

The DISTINCT keyword is used to remove duplicate values from query results.

Basic Syntax

SELECT DISTINCT column_name
FROM table_name;

---

15. DISTINCT Example

Suppose the customers table contains:

id	name	city
1	Rahul	Raipur
2	Amit	Durg
3	Priya	Raipur
4	Neha	Bilaspur
5	Ramesh	Durg

If we execute:

SELECT city
FROM customers;

The result can contain duplicate cities:

Raipur
Durg
Raipur
Bilaspur
Durg

Using DISTINCT:

SELECT DISTINCT city
FROM customers;

Result:

Raipur
Durg
Bilaspur

Duplicate city names are removed.

---

16. DISTINCT with ORDER BY

You can combine DISTINCT with ORDER BY.

For example:

SELECT DISTINCT city
FROM customers
ORDER BY city ASC;

This returns unique cities in alphabetical order.

Example:

Bilaspur
Durg
Raipur

---

17. DISTINCT with Multiple Columns

You can use DISTINCT with multiple columns.

SELECT DISTINCT city, service
FROM leads;

Here MySQL considers the combination of city and service.

For example:

city	service
Raipur	Website
Raipur	SEO
Raipur	Website
Durg	Website

Query:

SELECT DISTINCT city, service
FROM leads;

Result:

city	service
Raipur	Website
Raipur	SEO
Durg	Website

Only the duplicate combination is removed.

---

18. Real-Time CRM Example

Suppose a CRM contains a leads table:

CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    service VARCHAR(100),
    city VARCHAR(50),
    status VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Suppose it contains:

id	name	service	city	status
1	Rahul	Website	Raipur	New
2	Amit	SEO	Bilaspur	Follow Up
3	Priya	Website	Raipur	Converted
4	Neha	Google Ads	Durg	New
5	Ramesh	SEO	Raipur	New

---

Show latest leads first

SELECT *
FROM leads
ORDER BY created_at DESC;

This is useful for a CRM dashboard where the newest enquiries should appear first.

---

Show the 10 latest leads

SELECT *
FROM leads
ORDER BY created_at DESC
LIMIT 10;

---

Show the latest 5 new leads

SELECT *
FROM leads
WHERE status = 'New'
ORDER BY created_at DESC
LIMIT 5;

This is a very practical CRM query.

---

19. Real-Time E-Commerce Example

Suppose an online store has thousands of products.

The customer wants:

Category: Laptop
Sort: Price Low to High
Products per page: 10

The query can be:

SELECT *
FROM products
WHERE category = 'Laptop'
ORDER BY price ASC
LIMIT 10;

For the next page:

SELECT *
FROM products
WHERE category = 'Laptop'
ORDER BY price ASC
LIMIT 10, 10;

This combines filtering, sorting, and pagination.

---

20. Real-Time Customer Example

Suppose you want to display the latest 10 customers:

SELECT *
FROM customers
ORDER BY created_at DESC
LIMIT 10;

To display the oldest 10 customers:

SELECT *
FROM customers
ORDER BY created_at ASC
LIMIT 10;

---

21. Real-Time City Filter

Suppose a CRM needs to show all unique cities where customers are located.

SELECT DISTINCT city
FROM customers
ORDER BY city ASC;

This can be used to populate a dropdown:

Select City
----------------
Bilaspur
Durg
Raipur
Rajnandgaon

Instead of manually entering city names, the application can retrieve them directly from MySQL.

---

22. Combining WHERE, ORDER BY, LIMIT and DISTINCT

These clauses can work together.

For example:

SELECT DISTINCT city
FROM customers
WHERE status = 'Active'
ORDER BY city ASC
LIMIT 10;

This query:

1. Finds active customers

2. Gets unique cities

3. Sorts cities alphabetically

4. Returns only the first 10 cities

---

23. Order of SQL Clauses

When writing queries with these clauses, remember the general order:

SELECT
FROM
WHERE
ORDER BY
LIMIT

For example:

SELECT *
FROM products
WHERE stock > 0
ORDER BY price DESC
LIMIT 10;

The logical idea is:

Get data
   ↓
Filter data
   ↓
Sort data
   ↓
Limit results

DISTINCT is written immediately after SELECT:

SELECT DISTINCT city
FROM customers
ORDER BY city;

---

24. Common Beginner Mistakes

Mistake 1 — Forgetting ASC or DESC

This is valid:

SELECT *
FROM products
ORDER BY price;

Because ASC is the default.

But writing it explicitly can make the query easier to understand:

SELECT *
FROM products
ORDER BY price ASC;

---

Mistake 2 — Using LIMIT Before ORDER BY

Incorrect SQL structure:

SELECT *
FROM products
LIMIT 5
ORDER BY price DESC;

Correct:

SELECT *
FROM products
ORDER BY price DESC
LIMIT 5;

---

Mistake 3 — Assuming LIMIT Gives Random Records

This query:

SELECT *
FROM products
LIMIT 5;

does not mean "give me the 5 newest products" or "5 cheapest products."

If you need a specific order, use ORDER BY.

For example:

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 5;

---

Mistake 4 — Using DISTINCT on the Wrong Columns

Consider:

SELECT DISTINCT city, name
FROM customers;

This does not mean "unique cities."

It means MySQL returns unique city + name combinations.

If you need unique cities:

SELECT DISTINCT city
FROM customers;

---

25. Quick Reference

Requirement	Query
Ascending order	ORDER BY column ASC
Descending order	ORDER BY column DESC
First 10 records	LIMIT 10
Skip 10, get next 10	LIMIT 10, 10
Remove duplicates	SELECT DISTINCT column
Latest 5 records	ORDER BY created_at DESC LIMIT 5
Cheapest 5 products	ORDER BY price ASC LIMIT 5
Most expensive 5	ORDER BY price DESC LIMIT 5
Unique cities	SELECT DISTINCT city

---

26. Practice Exercises

Create a products table with fields such as:

id
name
category
price
stock
created_at

Then practice the following queries.

Exercise 1

Display all products from lowest price to highest price.

Exercise 2

Display all products from highest price to lowest price.

Exercise 3

Display only the first 10 products.

Exercise 4

Display the 5 most expensive products.

Exercise 5

Display the 5 cheapest products that are currently in stock.

Exercise 6

Display all unique product categories.

Exercise 7

Display unique categories in alphabetical order.

Exercise 8

Display page 2 with 10 products per page.

Exercise 9

Display the latest 10 products.

Exercise 10

Display the latest 5 products from the Mobile category.

---

27. Practical Query Challenge

Suppose an e-commerce website wants to show:

The 10 cheapest laptops that are currently in stock.

Write the query:

SELECT *
FROM products
WHERE category = 'Laptop'
AND stock > 0
ORDER BY price ASC
LIMIT 10;

This single query combines:

WHERE
AND
ORDER BY
ASC
LIMIT

These combinations are commonly used in real-world applications.

---

28. Conclusion

ORDER BY, LIMIT, and DISTINCT are important MySQL clauses for managing query results.

ORDER BY

Used for sorting:

ORDER BY price ASC;

or:

ORDER BY price DESC;

LIMIT

Used to restrict the number of records:

LIMIT 10;

It is also very useful for pagination:

LIMIT 10, 10;

DISTINCT

Used to remove duplicate values:

SELECT DISTINCT city
FROM customers;

Together, these features are used extensively in:

• E-commerce websites

• CRM systems

• Admin dashboards

• Customer management systems

• Search results

• Product listings

• Reports

• Pagination

Once you understand these three concepts, you can control how database results are sorted, limited, and displayed without unnecessary duplicate data.

What's Next?

In the next tutorial, we will learn:

MySQL Aggregate Functions: COUNT, SUM, AVG, MIN & MAX with Examples

We will cover how to calculate totals, counts, averages, minimum values, and maximum values using real-world business and website examples.