← Back to Curriculum

Advanced Filtering and Sorting

📚 Lesson 8 of 15 ⏱️ 45 min

Advanced Filtering and Sorting

45 min

The `ORDER BY` clause sorts the result set in ascending (ASC) or descending (DESC) order. ORDER BY is the last clause executed in a query (except LIMIT/OFFSET). You can sort by column names, column positions, or expressions. Understanding ORDER BY enables you to control result presentation. Sorting can impact performance on large datasets, especially without indexes on sort columns.

You can sort by multiple columns with different sort orders for each. SQL sorts by the first column, then by the second column for rows with equal values in the first, and so on. For example, `ORDER BY LastName ASC, FirstName ASC` sorts by last name first, then first name. Understanding multi-column sorting enables you to create sophisticated sort orders.

The `LIMIT` clause (or `TOP` in SQL Server) restricts the number of rows returned. LIMIT is useful for getting top N results, testing queries, and implementing pagination. LIMIT improves performance by reducing data transfer. Understanding LIMIT helps you control result set size. LIMIT syntax varies by database (MySQL uses LIMIT, SQL Server uses TOP, PostgreSQL supports both).

The `OFFSET` clause skips a specified number of rows before starting to return rows. OFFSET is used with LIMIT for pagination. For example, `LIMIT 10 OFFSET 20` skips the first 20 rows and returns the next 10. Understanding OFFSET enables you to implement pagination. OFFSET can be slow on large datasets because the database must still process skipped rows.

Combining `LIMIT` and `OFFSET` enables pagination of results, allowing you to retrieve data in chunks. Pagination is essential for web applications displaying large datasets. Common patterns include `LIMIT page_size OFFSET (page_number - 1) * page_size`. Understanding pagination helps you build user-friendly applications. However, OFFSET can become slow for large offsets—consider cursor-based pagination for better performance.

Best practices include using indexes on sort columns for better performance, avoiding large OFFSET values (consider cursor-based pagination), using LIMIT to prevent accidentally returning huge result sets, and understanding that NULL values sort differently in different databases. Understanding sorting and pagination enables you to build efficient, user-friendly data retrieval systems.

Key Concepts

  • ORDER BY sorts result sets in ascending or descending order.
  • Multiple columns can be sorted with different sort orders.
  • LIMIT restricts the number of rows returned.
  • OFFSET skips rows before returning results.
  • LIMIT and OFFSET together enable pagination.

Learning Objectives

Master

  • Sorting results with ORDER BY
  • Sorting by multiple columns
  • Limiting results with LIMIT/TOP
  • Implementing pagination with LIMIT and OFFSET

Develop

  • Result presentation thinking
  • Understanding pagination strategies
  • Designing efficient data retrieval systems

Tips

  • Use indexes on sort columns for better performance.
  • Use LIMIT to prevent accidentally returning huge result sets.
  • Consider cursor-based pagination for large datasets instead of large OFFSET values.
  • Understand that NULL values sort differently in different databases.

Common Pitfalls

  • Sorting without indexes on large tables, causing slow queries.
  • Using large OFFSET values, causing performance issues.
  • Not using LIMIT, accidentally returning huge result sets.
  • Not understanding multi-column sort order, getting unexpected results.

Summary

  • ORDER BY sorts result sets by specified columns.
  • LIMIT restricts the number of rows returned.
  • OFFSET skips rows before returning results.
  • LIMIT and OFFSET together enable pagination.
  • Understanding sorting and pagination enables efficient data retrieval.

Exercise

Write a query to get the top 10 most expensive products, sorted by price in descending order.

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC
LIMIT 10;

Code Editor

Output