Skip to main content
MySQL Basics
CHAPTER 08 Beginner

Sorting, Limiting, and Searching Data

Updated: May 16, 2026
6 min read

# CHAPTER 8

Sorting, Limiting, and Searching Data

1. Introduction

In Chapter 7, we learned how to extract raw data using SELECT and WHERE. However, when you search for "Laptops" on Amazon, the database doesn't just vomit a random list of products onto your screen. It displays exactly 20 products per page (LIMIT), sorted by the Highest Price (ORDER BY), and matches laptops that contain the word "Gaming" somewhere in the title (LIKE). In this chapter, we will learn how to format our raw data into user-friendly layouts.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Sort data alphabetically or numerically using ORDER BY.
  • Constrain the number of returned rows using LIMIT.
  • Understand pagination using OFFSET.
  • Perform fuzzy text searches using the LIKE operator and % wildcards.

3. Sorting Data (ORDER BY)

By default, MySQL returns rows in the exact order they were inserted into the hard drive. To sort them logically, we append the ORDER BY clause to the very end of our query.
  • ASC (Ascending): A to Z, or 1 to 100. (This is the default).
  • DESC (Descending): Z to A, or 100 to 1.
sql
1234567 
-- Get all employees, sorted alphabetically by First Name
SELECT * FROM employees 
ORDER BY first_name ASC;

-- Get all employees, sorted by the highest paid to the lowest paid
SELECT * FROM employees 
ORDER BY salary DESC;

4. Limiting Results (LIMIT)

If a table has 5 million rows, running a SELECT query might crash your terminal as it tries to print them all. We use LIMIT to restrict the maximum number of rows returned.
sql
123456 
-- Find the absolute highest paid employee in the company
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 1;

-- Output: Returns ONLY Anna, because she is sorted to the top and we limited to 1 row.

5. Pagination (OFFSET)

When you click "Page 2" on a web store, the backend database uses OFFSET to skip the first batch of products.
sql
1234 
-- Give me 10 products, but skip the first 20. 
-- (This effectively displays Page 3 of the results!)
SELECT * FROM products 
LIMIT 10 OFFSET 20;

6. Fuzzy Searching (LIKE and Wildcards)

The = operator is strictly exact. WHERE name = 'John' will NOT find "Johnathan". To perform fuzzy text searches (like a search bar on a website), we use the LIKE operator combined with the % wildcard. The % symbol means "any number of characters can go here".
sql
12345678 
-- 1. Starts With: Find anyone whose name begins with 'Jo' (John, Joseph, Joanna)
SELECT * FROM employees WHERE first_name LIKE 'Jo%';

-- 2. Ends With: Find anyone whose email ends with '.edu'
SELECT * FROM employees WHERE email LIKE '%.edu';

-- 3. Contains: Find any product that has the word 'Laptop' anywhere in the title
SELECT * FROM products WHERE product_name LIKE '%Laptop%';

7. Mini Project: Product Search System

You are building the backend search engine for an E-commerce store. A user searches for the word "Phone", clicks "Sort by Lowest Price", and wants to see the first 5 results.
sql
123456 
-- The perfect E-commerce query:
SELECT product_name, price 
FROM products 
WHERE product_name LIKE '%Phone%' 
ORDER BY price ASC 
LIMIT 5;

8. The Danger of Leading Wildcards

CRITICAL PERFORMANCE WARNING: When you query LIKE 'Jo%', MySQL uses its internal Indexes (like the index of a book) to instantly jump to the J's. It is incredibly fast. When you query LIKE '%Phone%' (putting a % at the very beginning), MySQL *cannot* use the Index. It is forced to perform a Full Table Scan, physically reading every single letter of every single row in the database. On a table with 10 million rows, a leading wildcard search will cause the server to hang and potentially crash.

9. Best Practices

  • Clause Order Matters: SQL clauses must be written in a very specific order, or the code will crash. The golden rule of order is:
  1. 1. SELECT
  1. 2. FROM
  1. 3. WHERE
  1. 4. ORDER BY
  1. 5. LIMIT

10. Exercises

  1. 1. Write a query to find the 3 youngest (most recently hired) employees in the employees table. (Hint: sort the date descending).
  1. 2. Write a query to find all users whose email address starts with "admin".

11. MCQ Quiz with Answers

Question 1

If you want to sort a numerical column from the largest number to the smallest number, which keyword must follow the ORDER BY clause?

Question 2

What is the mathematical and performance problem with executing WHERE name LIKE '%Smith' on a massive database?

12. Interview Questions

  • Q: Explain how a web application implements "Pagination" (Page 1, Page 2, Page 3) on the backend using SQL commands.
  • Q: Describe the performance implications of utilizing the LIKE operator with leading wildcards versus trailing wildcards.

13. FAQs

Q: Can I sort by multiple columns at once? A: Yes! ORDER BY department ASC, salary DESC; This will first group everyone alphabetically by department. Then, *within* each department, it will sort the employees by the highest salary to the lowest!

14. Summary

Raw data is useless to an end-user. By chaining the ORDER BY and LIMIT clauses, you transform chaotic data blocks into structured, paginated "Top 10" lists. By mastering the LIKE operator, you empower your applications with flexible search engine capabilities.

15. Next Chapter Recommendation

Our WHERE clauses have been relatively simple so far. But what if we need to find employees who work in Sales AND make over $80,000, OR work in IT but do NOT have a company car? In Chapter 9: SQL Operators and Conditions, we will master complex boolean logic.

Finish this Chapter

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

Previous Chapter SELECT Queries and Filtering Data
Next Chapter SQL Operators and Conditions

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6