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 students by last name
SELECT student_id, first_name, last_name, enrollment_year
FROM student
ORDER BY last_name;

This query returns students 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 by course credits descending
SELECT course_code, course_title, credits
FROM course
ORDER BY credits DESC;

This orders the course catalog so classes with the most credits 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 courses by department then code
SELECT dept_id, course_code, course_title, credits
FROM course
ORDER BY dept_id ASC, course_code ASC;

This query first groups courses by department, then within each department, sorts by course code alphabetically.

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 student cohorts
SELECT first_name, last_name, enrollment_year
FROM student
ORDER BY enrollment_year DESC
LIMIT 5;

LIMIT with OFFSET​

Combine LIMIT with OFFSET to implement pagination:

Second page of 5 students
SELECT student_id, first_name, last_name
FROM student
ORDER BY student_id
LIMIT 5 OFFSET 5;

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

Database Variations

Different database systems use different syntax for limiting results:

  • MySQL, PostgreSQL, SQLite: 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 Enrollment Activity​

5 most recent enrollment records
SELECT e.enroll_id,
       s.first_name || ' ' || s.last_name AS student_name,
       c.course_code,
       e.semester,
       e.grade
FROM enrollment AS e
JOIN student AS s ON s.student_id = e.student_id
JOIN course  AS c ON c.course_id  = e.course_id
ORDER BY e.semester DESC, e.enroll_id DESC
LIMIT 5;

Reviewing Rigorous Courses​

Top 3 courses by credits then code
SELECT course_code, course_title, credits, dept_id
FROM course
ORDER BY credits DESC, course_code 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 four-credit courses first" or "display the 5 most recent enrollment records."

Quiz​

Show quiz
  1. What clause would you use to display courses from highest to lowest credit value?
  2. How would you get the second page of results (rows 6-10) when displaying 5 students per page?
  3. Why is it important to use ORDER BY when using LIMIT to list the "top" students by enrollment year?
  4. What's wrong with this query: SELECT course_code FROM course LIMIT 5; when trying to find the most rigorous courses?
  5. How would you sort students by department (smallest dept_id first) and within each department by last name descending?

Answers:

  1. ORDER BY credits 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 credits (or another metric). Should be: SELECT course_code FROM course ORDER BY credits DESC LIMIT 5;
  5. ORDER BY dept_id ASC, last_name DESC