Skip to main content

Sorting and Limiting Results

Now that you can filter data using the WHERE clause, let's explore how to control the order and quantity of your query results. In this lesson, you'll learn to sort data meaningfully and limit output to exactly what you need.

Learning Goals:

  • Sort query results using ORDER BY clause
  • Control sort direction (ASC/DESC)
  • Limit the number of returned rows
  • Combine sorting and limiting effectively

The ORDER BY Clause​

The ORDER BY clause allows you to sort your query results based on one or more columns. This transforms your data from random ordering to meaningful sequences that reveal patterns and insights.

Basic Single-Column Sorting​

Sort customers by last name
SELECT customer_id, first_name, last_name, signup_year
FROM customer
ORDER BY last_name;

This query returns customers alphabetically by their last names in ascending order (A to Z).

Specifying Sort Direction​

You can control the sort direction using ASC (ascending) or DESC (descending):

Sort products by price descending
SELECT sku, product_name, price
FROM product
ORDER BY price DESC;

This orders the catalog so the most expensive items appear first.

tip

When no direction is specified, SQL defaults to ASC (ascending). For dates, this means oldest to newest; for numbers, smallest to largest; for text, A to Z.

Multi-Column Sorting​

You can sort by multiple columns to create more sophisticated ordering:

Sort products by category then SKU
SELECT category_id, sku, product_name, price
FROM product
ORDER BY category_id ASC, sku ASC;

This query first groups products by category, then within each category, sorts by SKU.

The LIMIT Clause​

The LIMIT clause restricts the number of rows returned by your query, which is especially useful for:

  • Displaying top N records
  • Implementing pagination
  • Testing queries on large datasets
Get 5 most recent customer signups
SELECT first_name, last_name, signup_year
FROM customer
ORDER BY signup_year DESC
LIMIT 5;

LIMIT with OFFSET​

Combine LIMIT with OFFSET to implement pagination:

Second page of 5 customers
SELECT customer_id, first_name, last_name
FROM customer
ORDER BY customer_id
LIMIT 5 OFFSET 5;

This skips the first 5 records and returns the next 5, perfect for showing page 2 of the customer list.

Database Variations

Different database systems use different syntax for limiting results:

  • MySQL: LIMIT count OFFSET start or LIMIT start, count
  • PostgreSQL: LIMIT count OFFSET start
  • SQL Server: OFFSET start ROWS FETCH NEXT count ROWS ONLY
  • Oracle: OFFSET start ROWS FETCH NEXT count ROWS ONLY

Practical Examples​

Highlighting Recent Order Activity​

5 most recent order items
SELECT oi.order_item_id,
       CONCAT(cust.first_name, ' ', cust.last_name) AS customer_name,
       p.sku,
       o.order_date,
       o.status
FROM order_item AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN customer AS cust ON cust.customer_id = o.customer_id
JOIN product AS p ON p.product_id = oi.product_id
ORDER BY o.order_date DESC, o.order_id DESC
LIMIT 5;

Reviewing High-Priced Products​

Top 3 products by price then SKU
SELECT sku, product_name, price, category_id
FROM product
ORDER BY price DESC, sku ASC
LIMIT 3;
Performance Consideration

Using LIMIT without ORDER BY can produce unpredictable results since databases don't guarantee row order without explicit sorting. Always use ORDER BY when the sequence matters.

Common Pitfalls​

  • Missing ORDER BY with LIMIT: Without explicit sorting, the "top" records returned by LIMIT may vary between executions
  • Confusing OFFSET numbering: OFFSET 10 skips the first 10 rows (rows 0-9), starting at row 10
  • Overusing LIMIT in production: While great for testing, ensure your application logic handles full datasets appropriately
  • Mixed sort directions: When using multiple columns, clearly specify direction for each to avoid confusion
  • Case sensitivity in text sorting: Some databases sort 'apple', 'Apple', 'APPLE' differently based on collation settings

Summary​

You now have powerful tools to control how your query results are presented:

  • ORDER BY organizes data in meaningful sequences
  • ASC/DESC controls sort direction
  • LIMIT restricts the number of returned rows
  • OFFSET enables pagination by skipping rows

These clauses work beautifully together to help you answer questions like "show me the most expensive products first" or "display the 5 most recent order items."

Quiz​

Show quiz
  1. What clause would you use to display products from highest to lowest price?
  2. How would you get the second page of results (rows 6-10) when displaying 5 customers per page?
  3. Why is it important to use ORDER BY when using LIMIT to list the "top" products by price?
  4. What's wrong with this query: SELECT sku FROM product LIMIT 5; when trying to find the most expensive products?
  5. How would you sort products by category (smallest category_id first) and within each category by SKU descending?

Answers:

  1. ORDER BY price DESC
  2. LIMIT 5 OFFSET 5
  3. Without ORDER BY the database doesn't guarantee which rows are considered "top" and results may be unpredictable
  4. Missing ORDER BY clause to sort by price. Should be: SELECT sku FROM product ORDER BY price DESC LIMIT 5;
  5. ORDER BY category_id ASC, sku DESC