Skip to main content
PostgreSQL
CHAPTER 08 Intermediate

Sorting, Searching, and Limiting Results

Updated: May 16, 2026
6 min read

# CHAPTER 8

Sorting, Searching, and Limiting Results

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 (ILIKE). 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.
  • Perform case-insensitive searches using the PostgreSQL ILIKE operator.

3. Sorting Data (ORDER BY)

By default, PostgreSQL returns rows in the exact order it reads them off the hard drive (which is chaotic). 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 the single highest-paid person.

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
12345 
-- Give me 10 products, but skip the first 20. 
-- (This effectively displays Page 3 of the results!)
SELECT * FROM products 
ORDER BY price ASC
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
12345 
-- Starts With: Find anyone whose name begins with 'Jo' (John, Joseph, Joanna)
SELECT * FROM employees WHERE first_name LIKE 'Jo%';

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

7. The PostgreSQL Superpower: ILIKE

In standard SQL, LIKE is case-sensitive. LIKE '%laptop%' will NOT find "Gaming Laptop". To fix this, PostgreSQL provides the ILIKE operator (Insensitive Like). It ignores capitalization entirely!
sql
12 
-- Contains: Find any product that has the word 'Laptop' anywhere in the title, regardless of capitalization!
SELECT * FROM products WHERE product_name ILIKE '%laptop%';

8. 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 ILIKE '%phone%' 
ORDER BY price ASC 
LIMIT 5;

9. The Danger of Leading Wildcards

CRITICAL PERFORMANCE WARNING: When you query ILIKE 'Jo%', PostgreSQL uses its internal Indexes to instantly jump to the J's. It is incredibly fast. When you query ILIKE '%Phone%' (putting a % at the very beginning), PostgreSQL *cannot* use a standard 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 massive server lag.

10. 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 / OFFSET

11. 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", regardless of capitalization.

12. SQL Challenges

Write a query to retrieve employees sorted first by their department alphabetically, and then by their salary from highest to lowest within that department.
sql
123 
SELECT first_name, department, salary 
FROM employees 
ORDER BY department ASC, salary DESC;

13. MCQ Quiz with Answers

Question 1

What is the fundamental difference between LIKE and ILIKE in PostgreSQL?

Question 2

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

14. 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 ILIKE operator with leading wildcards versus trailing wildcards.

15. FAQs

Q: Does MySQL have ILIKE? A: No! ILIKE is a PostgreSQL-specific extension. In standard MySQL, standard LIKE is usually case-insensitive by default depending on the table collation.

16. 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 ILIKE operator, you empower your applications with flexible search engine capabilities.

17. 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 Conditional Statements, 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 Conditional Statements

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6