ORDER BY, LIMIT & DISTINCT

Sort, limit, and deduplicate your results

← Lab 2: WHERE Lab 3 of 10 Lab 4: Aggregates →

⏳ Loading SQL engine...

📋 DATABASE SCHEMA — Available Tables

TABLE: students (id, name, major, gpa, year, scholarship, city, age)

📖 Concept Recap

ORDER BY column ASC — sorts A→Z, 0→9 (ASC is the default)
ORDER BY column DESC — sorts Z→A, 9→0
ORDER BY col1, col2 — sort by multiple columns; second column breaks ties
LIMIT n — return only the first n rows (comes after ORDER BY)
SELECT DISTINCT column — removes duplicate values in the result

Full clause order: SELECT → FROM → WHERE → ORDER BY → LIMIT

👀 Worked Example

-- Top 3 students by GPA SELECT name, major, gpa FROM students ORDER BY gpa DESC LIMIT 3; -- All unique majors (no duplicates) SELECT DISTINCT major FROM students ORDER BY major; -- Students sorted by year, then by GPA within each year SELECT name, year, gpa FROM students ORDER BY year ASC, gpa DESC;

✏️ Guided

Exercise 1 — Lowest GPA Students

Fill in the blanks to find the 3 students with the lowest GPA (students who may need academic support).

-- Bottom 3 GPA students
SELECT name, major, gpa
FROM students
ORDER BY gpa ___   -- ascending or descending?
LIMIT ___;

💡 Hint: To get the lowest GPA first, sort ascending (ASC). Then limit to 3 rows.

💪 Independent

Exercise 2 — City Top Students

Write a query that sorts students by city (A→Z), then by GPA (highest first) within each city. Show name, city, and GPA. Note what you observe — we’ll learn how to get exactly one per city with GROUP BY later.

-- Sort by city, then by GPA within each city

💡 Hint: ORDER BY city ASC, gpa DESC — two columns separated by a comma.

🔥 Challenge

Exercise 3 — DISTINCT Combinations

Write two queries:

Unique cities where students live, ordered alphabetically
Unique major + year combinations (use both columns in SELECT with DISTINCT), ordered by major then year

-- Query 1: Unique cities alphabetically
SELECT DISTINCT city FROM students ORDER BY city;

-- Query 2: Unique major + year combinations

💡 Hint: SELECT DISTINCT major, year FROM students ORDER BY major, year; — DISTINCT applies to the combination of both columns.

🏆 Mini Project

Mini Project — Student Directory

You’re building a student directory. Write 4 queries:

Full directory A–Z — all students sorted by name ascending
Dean’s list — top 3 students by GPA (use LIMIT 3)
Freshmen roster — only year = 1 students, sorted by name
Unique majors offered — use DISTINCT, show distinct major names ordered alphabetically

-- Query 1: Full directory A-Z


-- Query 2: Dean's list (top 3 by GPA)


-- Query 3: Freshmen roster


-- Query 4: Unique majors offered

✅ Lab 3 Complete!

You can now sort results, limit output, and eliminate duplicates. These three tools are essential in every analyst’s toolkit. Next up: aggregate functions — where we turn rows into insights.

Continue to Lab 4: Aggregate Functions →

← Lab 2: WHERE Lab 3 of 10 Lab 4: Aggregates →