Skip to main content
SQL Fundamentals
CHAPTER 10 Beginner

Sorting and Limiting Results

Updated: May 16, 2026
15 min read

# CHAPTER 10

Sorting and Limiting Results

1. Introduction

If you search Amazon for "Laptops", the database might find 5,000 matches. If the database sends all 5,000 laptops to your phone at once, in a completely random order, your phone will crash and the data will be useless. To solve this, web applications force the database to arrange data logically (e.g., "Highest Price to Lowest Price") and restrict the output to small chunks (e.g., "Show me the first 20 items"). In this chapter, we will master the ORDER BY and LIMIT clauses to organize our data for the frontend UI.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • 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

When you execute a standard SELECT * FROM users; query, SQL returns the rows in whatever order it finds them on the physical hard drive. It is inherently chaotic. You can *never* assume the output will be sorted by ID or by Name unless you explicitly command the database to sort it.

4. Arranging Data (ORDER BY)

To mathematically or alphabetically sort your output grid, you append the ORDER BY clause to the very end of your query.
sql
123 
-- Fetch all employees and arrange them alphabetically by their last name
SELECT * FROM employees 
ORDER BY last_name;

5. Ascending (ASC) vs Descending (DESC)

By default, ORDER BY always sorts in Ascending order (A to Z, 1 to 99, Oldest to Newest). If you want to reverse the order (Z to A, Highest to Lowest), you must explicitly use the DESC keyword.
sql
1234567 
-- 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

What happens if you sort 1,000 employees by last_name, and 50 of them have the last name "Smith"? The 50 Smiths will be grouped together, but scrambled internally. You can apply secondary sorting levels!
sql
1234 
-- 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)

To prevent crashing your frontend application with massive payloads, you should almost always restrict the maximum number of rows returned using the LIMIT clause.
sql
123 
-- 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)

By combining ORDER BY and LIMIT, you can generate powerful analytical leaderboards instantly!
sql
123456 
-- 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)

If a user is browsing products and clicks "Page 2" at the bottom of the screen, how does the backend know to skip the 20 products they already looked at on Page 1? We use the OFFSET keyword. It tells SQL to literally step over a certain number of rows before it starts collecting data.
sql
12345678 
-- 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.
*Crash:* SELECT * ORDER BY age FROM users LIMIT 5 WHERE age > 18; *Correct:* SELECT * FROM users WHERE age > 18 ORDER BY age LIMIT 5;

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

Write a single SQL query to find all "Action" movies from the movies table, sort them by rating from highest to lowest, and return exactly the top 5 results.
sql
1234 
SELECT * FROM movies
WHERE genre = 'Action'
ORDER BY rating DESC
LIMIT 5;

14. MCQ Quiz with Answers

Question 1

In standard SQL syntax, what is the strict order of operations for query clauses?

Question 2

When a web application executes a query utilizing OFFSET 100 LIMIT 50, what exactly is the database instructed to do?

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

Q: Do all SQL databases use LIMIT and OFFSET? A: MySQL, PostgreSQL, and SQLite use LIMIT and OFFSET. However, Microsoft SQL Server (T-SQL) and Oracle use different, more verbose syntaxes (like OFFSET ... FETCH NEXT ... ROWS ONLY or ROWNUM). The core concept is identical, but the specific wording changes!

17. Summary

By mastering the formatting clauses, you have taken control of how data flows from the database to the client UI. You can architect analytical leaderboards using ORDER BY DESC and LIMIT, and you can build the backend foundations for modern, paginated web applications utilizing dynamic OFFSET math.

18. Next Chapter Recommendation

Our queries are robust, but what happens when a row is missing data? If an employee doesn't have a phone number, is the column filled with a zero, a blank space, or something else entirely? In Chapter 11: Working with NULL Values, we will explore the absolute void of data and how to handle it mathematically.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter SQL WHERE Clause | Filter Database Records
Next Chapter SQL NULL Values | IS NULL and IS NOT NULL Operators

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6