ORDER BY Clause

The ORDER BY keyword is used to sort the result set of a query in ascending or descending order. If an order is not specified, the database may return the rows in any order, which can be unpredictable.

Database Preparation

Run this block to set up the Books table.

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 'Fantasy', 1937),
       (2, '1984', 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 'Fantasy', 1954),
       (4, 'Dune', 'Sci-Fi', 1965);

SELECT * FROM Books;

1. Default Order (Ascending)

Query:

SELECT Title, Genre
FROM Books
ORDER BY Title ASC; -- The ASC is optional here

2. Descending Order (DESC)

Query:

SELECT Title, PublishedYear
FROM Books
ORDER BY PublishedYear DESC;

3. Ordering by Multiple Columns

Query:

SELECT Genre, Title
FROM Books
ORDER BY Genre ASC, Title ASC;

Explanation:

1. The database first sorts all rows by Genre in ascending order ('Dystopian', 'Fantasy', 'Sci-Fi').
2. For the two rows where the Genre is 'Fantasy', it then applies the second sorting rule, ordering them by Title alphabetically. "The Fellowship of the Ring" comes before "The Hobbit".

Result:

Hands-on Exercise

Task: Sort the books by PublishedYear in descending order, and then by Title in ascending order.

Your Query:

-- Write your query here

Expected Results: