SQL ORDER BY and LIMIT | Sorting & Pagination

# CHAPTER 10

Sorting and Limiting Results #

1. Introduction #

2. Learning Objectives #

• Understand that SQL data is naturally unordered.

• Arrange results using the ORDER BY clause.

• Sort data ascending (ASC) and descending (DESC).

• Restrict the total number of returned rows using LIMIT.

• Architect a web pagination system using OFFSET.

3. The Natural Chaos of SQL #

4. Arranging Data (ORDER BY) #

-- Fetch all employees and arrange them alphabetically by their last name
SELECT * FROM employees 
ORDER BY last_name;

5. Ascending (ASC) vs Descending (DESC) #

-- Sort products by Price, Highest to Lowest!
SELECT product_name, price FROM products 
ORDER BY price DESC;

-- Sort users by Age, Youngest to Oldest!
SELECT * FROM users 
ORDER BY age ASC; -- (The ASC is technically optional, but good for readability)

6. Multi-Level Sorting #

-- 1. Sort by Last Name (A-Z)
-- 2. If the last names match exactly, sort those specific rows by Salary (Highest to Lowest)
SELECT * FROM employees 
ORDER BY last_name ASC, salary DESC;

7. Restricting Output (LIMIT) #

-- Give me ALL users, but only send the first 5 results over the network!
SELECT * FROM users 
LIMIT 5;

8. Combining Sort and Limit (Top 10 Leaderboards) #

-- Get the Top 3 highest-scoring players!
-- 1. Sort by score descending (Highest first)
-- 2. Chop the list off at 3 rows
SELECT username, score FROM players 
ORDER BY score DESC 
LIMIT 3;

9. Bypassing Data (OFFSET and Pagination) #

-- PAGE 1: Give me the first 20 products
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;

-- PAGE 2: Step over the first 20 products, and give me the NEXT 20!
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 20;

-- PAGE 3: Step over the first 40 products, give me the NEXT 20!
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

*(In standard web backends like PHP or Node.js, the math is always: OFFSET = (PageNumber - 1) * ItemsPerPage)*

10. Common Mistakes #

• Putting ORDER BY in the wrong place: SQL is strictly parsed. The ORDER BY clause must come *after* the WHERE clause, and the LIMIT clause must come at the absolute end of the query.

11. Best Practices #

• Sorting is Expensive: If you run ORDER BY price on a table with 5 million rows, the database has to load 5 million numbers into RAM, do the math, and organize them. This is very slow! Database administrators always create Indexes on columns that are frequently sorted to pre-organize them on the hard drive.

12. Exercises #

1. 1. What keyword forces ORDER BY to arrange data from Highest to Lowest?

1. 2. When building a pagination system, what keyword is paired with LIMIT to step over previous results?

13. SQL Challenges #

SELECT * FROM movies
WHERE genre = 'Action'
ORDER BY rating DESC
LIMIT 5;

14. MCQ Quiz with Answers #

15. Interview Questions #

• Q: Explain how you would architect a standard offset pagination system for a REST API using SQL LIMIT and OFFSET. Provide the mathematical formula for calculating the offset based on a requested Page Number.

• Q: A developer complains that their "Top 10 Users by Points" query is taking 8 seconds to run. Describe the mechanical reason why sorting a massive table without an Index is devastating to server performance.

16. FAQs #

17. Summary #

18. Next Chapter Recommendation #