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:

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:

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.