🧱 MySQL Sorting & Limiting (ORDER BY, LIMIT, DISTINCT)

---

🧲 Introduction – Why Learn Sorting & Limiting in MySQL?

Fetching data with SQL is only part of the job—how you organize and reduce the results matters just as much. MySQL offers powerful keywords like ORDER BY, LIMIT, and DISTINCT to help you sort, paginate, and eliminate duplicates in query results.

🎯 In this tutorial, you’ll learn:

• How to sort query results using ORDER BY
• How to limit the number of rows returned with LIMIT
• How to remove duplicate rows using DISTINCT
• Use cases for each operator with real-world examples

---

📘 What Are Sorting & Limiting Clauses in MySQL?

These SQL clauses control how data is displayed and how much of it is shown. Together, they make SQL queries more precise and efficient.

Clause	Purpose
ORDER BY	Sorts results by one or more columns
LIMIT	Restricts the number of returned rows
DISTINCT	Removes duplicate rows

---

🧪 MySQL ORDER BY – Sort Query Results

🔹 Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];

🔹 Example 1: Sort Customers by Name (A-Z)

SELECT name, city FROM customers
ORDER BY name ASC;

🔹 Example 2: Sort by Date (Latest First)

SELECT name, signup_date FROM users
ORDER BY signup_date DESC;

📘 Use ASC for ascending (default), DESC for descending order.

---

🎯 ORDER BY with Multiple Columns

SELECT name, city, age FROM employees
ORDER BY city ASC, age DESC;

📌 Sorts first by city (A-Z), then by age (highest first) within each city.

---

🚦 MySQL LIMIT – Control the Result Size

🔹 Syntax:

SELECT column1 FROM table_name
LIMIT number;

🔹 Example 1: Get Top 5 Products

SELECT * FROM products
ORDER BY rating DESC
LIMIT 5;

📌 Returns the 5 highest-rated products.

---

🔹 Pagination with LIMIT and OFFSET

SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 20;

📌 Returns 10 rows starting from the 21st record (offset starts from 0).

---

✨ MySQL DISTINCT – Eliminate Duplicates

🔹 Syntax:

SELECT DISTINCT column1 FROM table_name;

🔹 Example 1: Unique Cities from Customers

SELECT DISTINCT city FROM customers;

📌 Returns each city only once, even if multiple customers are from the same city.

---

🔹 Example 2: Multiple Columns with DISTINCT

SELECT DISTINCT country, city FROM customers;

📌 Returns unique pairs of country and city.

📘 DISTINCT applies to the entire row returned—not just individual columns.

---

⚖️ ORDER BY vs LIMIT vs DISTINCT – Quick Comparison

Keyword	Use Case	Example
ORDER BY	Sorts data	ORDER BY name ASC
LIMIT	Limits result rows	LIMIT 10
DISTINCT	Removes duplicate records	SELECT DISTINCT city FROM customers

---

🛠️ Real-World Use Cases

Scenario	SQL Example
📈 Top 10 highest-paid employees	SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
📍 Find all unique cities	SELECT DISTINCT city FROM users;
📅 Get recent 5 signups	SELECT * FROM users ORDER BY signup_date DESC LIMIT 5;
🧾 Paginate products page	SELECT * FROM products LIMIT 20 OFFSET 40;

---

📌 Summary – Recap & Next Steps

Sorting, limiting, and de-duplicating results are core techniques in data querying and reporting. Mastering ORDER BY, LIMIT, and DISTINCT helps you return only the data you actually need—neatly arranged and optimized.

🔍 Key Takeaways

• ORDER BY sorts results by columns in ascending or descending order
• LIMIT reduces the number of rows returned and supports pagination
• DISTINCT filters out duplicate rows for clean, unique results
• You can combine all three for powerful query output control

⚙️ Real-World Relevance

From paginated web interfaces to dashboards and reports, these clauses optimize user experience and system performance.

---

❓ FAQ – MySQL ORDER BY, LIMIT, DISTINCT

❓ Can I use ORDER BY and LIMIT together?

✅ Yes. It’s common to sort data and then limit how many results you see:

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

❓ How does DISTINCT work with multiple columns?

✅ It removes duplicate row combinations, not just individual column values.

❓ Can I sort by an alias or expression?

✅ Yes:

SELECT name, salary * 12 AS yearly_salary
FROM employees
ORDER BY yearly_salary DESC;

❓ What’s the difference between LIMIT and OFFSET?

✅ LIMIT controls the number of rows. OFFSET skips rows:

LIMIT 5 OFFSET 10  -- skips first 10, shows next 5

❓ Is DISTINCT faster than GROUP BY?

✅ It depends on the data and indexing. For simple use cases, both can be equally effective.

---

Share Now :