Lesson 3: Filtering and Sorting Query Results

In previous lessons, we learned how to retrieve data from a database using the SELECT statement and apply conditions using WHERE. Now, let's dive into more ways to refine your query results by:

• Eliminating duplicate rows from your results
• Sorting data in a meaningful order
• Returning just a portion of the results

These techniques are essential when working with large tables and improve both readability and performance. Think of them as the final polish that transforms raw data into useful, organized information.

🔁 Removing Duplicate Results with DISTINCT

Even if your table has unique rows, your query results might contain duplicates—especially when selecting values from a single column. The DISTINCT keyword helps remove repeated values and return only unique entries.

Basic format:

SELECT DISTINCT column_name 
FROM table_name;

This tells SQL to return each value of column_name only once.

Examples:

-- Get all unique directors (no duplicates)
SELECT DISTINCT director 
FROM oscar_movies;

-- Get unique combinations of genre and year
SELECT DISTINCT genre, year 
FROM oscar_movies;

💡 Key Point: If multiple columns are selected, DISTINCT will keep unique combinations of those columns. We'll look at more powerful grouping techniques using GROUP BY in an upcoming lesson.

🔤 Sorting Results with ORDER BY

Real-life databases are rarely ordered in a meaningful way. If we want our query results to be easier to read and interpret, we can sort them using the ORDER BY clause.

Basic format:

SELECT column1, column2 
FROM table_name 
ORDER BY column1 ASC;  -- or DESC

Sorting options:

• ASC sorts in ascending (A–Z or smallest to largest) order — this is the default
• DESC sorts in descending (Z–A or largest to smallest) order

Examples:

-- Sort movies by year (oldest first)
SELECT title, year 
FROM oscar_movies 
ORDER BY year ASC;

-- Sort movies by title (Z to A)
SELECT title, director 
FROM oscar_movies 
ORDER BY title DESC;

🔗 Sorting by Multiple Columns

You can sort by multiple columns by separating them with commas. SQL will sort by the first column, then break ties using the second column, and so on.

Example:

-- Sort by year first, then by title within each year
SELECT title, year, director 
FROM oscar_movies 
ORDER BY year ASC, title ASC;

-- Sort by director, then by year (newest first) for each director
SELECT title, director, year 
FROM oscar_movies 
ORDER BY director ASC, year DESC;

🔢 Limiting Results with LIMIT and OFFSET

When working with large result sets, you might want to see just a small portion — for instance, the top 5 rows or the next 10 records after skipping some. This is where LIMIT and OFFSET come in handy.

Basic format:

SELECT column1 
FROM table_name 
ORDER BY column1 
LIMIT number_rows OFFSET skip_rows;

What each part does:

• LIMIT specifies the number of rows to return
• OFFSET skips a number of rows before starting to return data

Examples:

-- Get the 5 most recent movies
SELECT title, year 
FROM oscar_movies 
ORDER BY year DESC 
LIMIT 5;

-- Get movies 6-10 (skip first 5, then show next 5)
SELECT title, year 
FROM oscar_movies 
ORDER BY year DESC 
LIMIT 5 OFFSET 5;

-- Get the first 3 movies alphabetically
SELECT title, director 
FROM oscar_movies 
ORDER BY title ASC 
LIMIT 3;

💡 Pro Tip: LIMIT and OFFSET are especially useful for creating pagination features like "page 1", "page 2", etc. For example, page 1 might show the first 10 results (LIMIT 10), page 2 would show the next 10 (LIMIT 10 OFFSET 10), and so on.

🔄 Combining All Techniques

You can combine DISTINCT, WHERE, ORDER BY, LIMIT, and OFFSET in a single query to create powerful, targeted results.

Example:

-- Get unique directors from dramas after 2000, 
-- sorted alphabetically, show only first 5
SELECT DISTINCT director 
FROM oscar_movies 
WHERE genre = 'Drama' AND year > 2000 
ORDER BY director ASC 
LIMIT 5;

Query execution order:

1. FROM - Get data from the table
2. WHERE - Filter rows based on conditions
3. SELECT - Choose which columns to display
4. DISTINCT - Remove duplicate rows
5. ORDER BY - Sort the results
6. LIMIT/OFFSET - Limit the number of results returned

✅ Key Takeaways

• Use DISTINCT to remove duplicate rows and return only unique values
• Use ORDER BY to sort results alphabetically or numerically, in ascending or descending order
• You can sort by multiple columns by separating them with commas
• Use LIMIT to specify how many rows to return
• Use OFFSET to skip a certain number of rows before returning data
• Combine techniques for powerful, targeted queries that return exactly what you need
• These clauses work together to refine your data retrieval for clarity and performance
• Always use ORDER BY with LIMIT to ensure consistent results

📝 Exercise Overview

In this lesson's practice section, you'll build SQL queries that use DISTINCT, ORDER BY, LIMIT, and OFFSET to control the shape and order of your result sets.

You'll explore how to:

• Return a list of unique values from a column
• Sort rows by text and numerical fields
• Fetch the top or bottom results in a list

Each exercise is based on the oscar_movies table, and designed to help you become more confident in shaping your query output to fit your needs. You won't just retrieve rows — you'll shape, sort, and streamline them!