Sorting and Limiting Results

You've learned to retrieve data with SELECT and filter it with WHERE. Now it's time to learn how to organize and control your results! ORDER BY lets you sort data (like A-Z or highest-to-lowest), while LIMIT lets you say "show me just the top 10." These are essential skills for creating reports, rankings, and user-friendly displays.

📚 What You'll Learn

• How ORDER BY changes the order of your results
• Using ASC (ascending) vs. DESC (descending)
• Sorting numbers, text, and dates
• Sorting by multiple columns (primary and secondary sort)
• Why ORDER BY must come before LIMIT
• How LIMIT controls the number of rows returned (MySQL)
• Using FETCH for Oracle databases (alternative to LIMIT)
• Combining WHERE, ORDER BY, and LIMIT together
• Common student mistakes and how to avoid them
• Hands-on practice exercises

1. What Does ORDER BY Do?

By default, SQL returns rows in no particular order—basically random! ORDER BY lets you sort results by one or more columns, making your data organized and meaningful.

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY column_name;

2. ASC vs. DESC: Choosing Sort Direction

When you sort, you can choose the direction:

• ASC (ascending): Smallest to largest, A to Z, oldest to newest
• DESC (descending): Largest to smallest, Z to A, newest to oldest

Example: Sorting by GPA

SELECT name, gpa
FROM students
ORDER BY gpa ASC;

SELECT name, gpa
FROM students
ORDER BY gpa DESC;

3. Sorting Numbers, Text, and Dates

Numbers

Numbers sort numerically (by value):

• ASC: 1, 2, 10, 100, 1000
• DESC: 1000, 100, 10, 2, 1

-- Find students with lowest GPAs first
SELECT name, gpa
FROM students
ORDER BY gpa ASC;

Text (Strings)

Text sorts alphabetically:

• ASC: A, B, C... Z (alphabetical order)
• DESC: Z, Y, X... A (reverse alphabetical)

-- Sort students alphabetically by name
SELECT name, major
FROM students
ORDER BY name ASC;

Dates

Dates sort chronologically:

• ASC: Oldest to newest (earliest date first)
• DESC: Newest to oldest (latest date first)

-- Show most recently enrolled students first
SELECT name, enrollment_date
FROM students
ORDER BY enrollment_date DESC;

4. Sorting by Multiple Columns

You can sort by multiple columns! SQL sorts by the first column, then uses additional columns as "tiebreakers."

SELECT name, major, gpa
FROM students
ORDER BY major ASC, gpa DESC;

This query says: "First, sort by major alphabetically. Within each major, sort by GPA from highest to lowest."

Example Result:

Notice how Biology students are grouped together (sorted alphabetically), and within Biology, Carol (3.9) appears before Emma (3.4) because of the DESC on GPA!

5. What Does LIMIT Do? (MySQL)

LIMIT restricts the number of rows returned by your query. It's essential for:

• Creating "Top 10" lists
• Pagination (showing 20 results per page)
• Previewing data (show me just 5 examples)
• Performance (don't load 1 million rows!)

Basic Syntax (MySQL)

SELECT column1, column2
FROM table_name
ORDER BY column_name
LIMIT number;

Example: Top 3 Students by GPA

SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 3;

This query says: "Sort students by GPA (highest first), then show me only the top 3."

Result:

Only 3 rows returned, even if the table has 100 students!

6. Using FETCH in Oracle Databases

Oracle Syntax (Modern - 12c+)

SELECT name, gpa
FROM students
ORDER BY gpa DESC
FETCH FIRST 3 ROWS ONLY;

Oracle Syntax (Legacy - ROWNUM)

SELECT * FROM (
    SELECT name, gpa
    FROM students
    ORDER BY gpa DESC
)
WHERE ROWNUM <= 3;

Side-by-Side Comparison

SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 3;

SELECT name, gpa
FROM students
ORDER BY gpa DESC
FETCH FIRST 3 ROWS ONLY;

7. Why ORDER BY Must Come Before LIMIT

Think about it logically: if you want the "top 10 students by GPA," you need to:

1. First: Sort all students by GPA (ORDER BY)
2. Then: Take the top 10 from that sorted list (LIMIT)

Without ORDER BY, LIMIT would just give you 10 random students—not the top 10!

Wrong: LIMIT Without ORDER BY

-- ❌ BAD: You'll get 10 random students, not the top 10!
SELECT name, gpa
FROM students
LIMIT 10;

Right: ORDER BY Before LIMIT

-- ✅ GOOD: Top 10 students by GPA
SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 10;

8. Combining WHERE, ORDER BY, and LIMIT

You can combine all three clauses! The order is always:

SELECT columns
FROM table
WHERE condition      -- Filter rows
ORDER BY column      -- Sort results
LIMIT number;        -- Limit quantity

Example: Top 5 Computer Science Students

SELECT name, major, gpa
FROM students
WHERE major = 'Computer Science'    -- Only CS majors
ORDER BY gpa DESC                   -- Highest GPA first
LIMIT 5;                            -- Top 5 only

This query says: "Find Computer Science majors, sort them by GPA (highest first), and show me only the top 5."

Execution Order (How SQL Processes It)

9. Common Student Mistakes

Mistake #1: Wrong Clause Order

-- ❌ WRONG: LIMIT before ORDER BY
SELECT name, gpa
FROM students
LIMIT 5
ORDER BY gpa DESC;  -- Syntax error!

-- ✅ CORRECT: ORDER BY before LIMIT
SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 5;

Mistake #2: Forgetting ORDER BY with LIMIT

-- ❌ BAD: Gets random 10 students, not top 10
SELECT name, gpa
FROM students
LIMIT 10;

-- ✅ GOOD: Gets actual top 10
SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 10;

Mistake #3: Sorting by Column Not in SELECT

-- ⚠️ CONFUSING: Sorts by gpa but doesn't show it
SELECT name, major
FROM students
ORDER BY gpa DESC;

-- ✅ CLEARER: Show what you're sorting by
SELECT name, major, gpa
FROM students
ORDER BY gpa DESC;

Note: The first query works but can confuse readers—why are these students in this order? Including the sort column makes it clear!

Mistake #4: Mixing Up ASC and DESC

-- ❌ WRONG: Gets lowest GPAs, not highest
SELECT name, gpa
FROM students
ORDER BY gpa ASC    -- Ascending = low to high
LIMIT 5;

-- ✅ CORRECT: Gets top 5 highest GPAs
SELECT name, gpa
FROM students
ORDER BY gpa DESC   -- Descending = high to low
LIMIT 5;

Mistake #5: Using LIMIT in Oracle

-- ❌ WRONG: Doesn't work in Oracle
SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 5;

-- ✅ CORRECT: Use FETCH in Oracle
SELECT name, gpa
FROM students
ORDER BY gpa DESC
FETCH FIRST 5 ROWS ONLY;

10. 🎯 Hands-On Practice Exercises

SELECT *
FROM students
ORDER BY name ASC;

SELECT name, gpa
FROM students
ORDER BY gpa DESC;

SELECT name, gpa
FROM students
ORDER BY gpa DESC
LIMIT 3;

SELECT name, major, gpa
FROM students
ORDER BY major ASC, gpa DESC;

SELECT name, major, gpa
FROM students
WHERE major = 'Biology'
ORDER BY gpa DESC
LIMIT 5;

SELECT name, enrollment_date
FROM students
ORDER BY enrollment_date DESC
LIMIT 10;

SELECT *
FROM students
ORDER BY gpa DESC
FETCH FIRST 10 ROWS ONLY;

📝 Key Takeaways

• ORDER BY sorts your results (ASC = ascending, DESC = descending)
• Default sort direction is ASC (smallest to largest, A to Z)
• You can sort by multiple columns: ORDER BY major ASC, gpa DESC
• Numbers, text, and dates all sort differently but logically
• LIMIT (MySQL) restricts the number of rows returned
• Oracle uses FETCH FIRST ... ROWS ONLY instead of LIMIT
• Always use ORDER BY before LIMIT for meaningful results
• Clause order: WHERE → ORDER BY → LIMIT
• Include sort columns in SELECT for clarity (helps readers understand ordering)
• Without ORDER BY, LIMIT gives you arbitrary rows (not useful!)