Lesson 7

Filtering and sorting SQL results

Eliminating Duplicate Rows with DISTINCT

When querying the books table, using SELECT without DISTINCT would return duplicate books for authors who have written multiple books:

SELECT author_id FROM books;

Output:

1
1
2
3
3
...

Adding the DISTINCT keyword eliminates these duplicates:

SELECT DISTINCT author_id FROM books;

Output:

1
2
3
...

Now we simply get unique author IDs.

Sorting Data with ORDER BY

The ORDER BY clause allows sorting query results in ascending or descending order based on column values.

  • ASC sorts values from lowest to highest
  • DESC sorts values from highest to lowest

For example, to retrieve customer records sorted by registration date:

SELECT * FROM customers
ORDER BY registration_date DESC;

Output:

ID Name Email Date
3 John Doe [email protected] 2022-01-05
9 Lisa Wong [email protected] 2021-11-15
12 Mark Smith [email protected] 2020-09-30

By default ORDER BY sorts ascending, so leaving out the DESC keyword would print oldest registrations first.

We can also chain multiple columns to prioritize sort order:

SELECT * FROM books
ORDER BY author_id, publish_date DESC;

Now it primarily sorts by author_id ascending. But for any books from the same author, it will sort by publish_date descending.

The ability to finely control ordering using ASC and DESC helps arrange outputs in the most logical way for analysis.

Constraining Rows with LIMIT and OFFSET

To select only 5 of the most recently published books:

SELECT * FROM books
ORDER BY publish_date DESC
LIMIT 5;

Output:

ID Title Pub Date
28 Art of Cooking 2023-02-15
64 World History 2023-02-12
49 Robotics for Beginners 2023-02-10
3 Psychology Basics 2023-02-05

We could implement pagination for results using OFFSET. Like getting the next 5:

LIMIT 5 OFFSET 5;

Execution Order

Important behaviors like LIMIT/OFFSET applying after sorting ensures proper row constraints when querying large tables like orders and order_details.

Retrieve book title and pubish date sorted by title in ascending order
Retrieve books title and price sorted by price in descending order
Retrieve the title and price of the top 5 most expensive books
Tables used in this quizz:
No Data

©2024 SQLZap - Learn SQL Interactively

Twitter