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.

DROP TABLE IF EXISTS Books;

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);

Database Overview & Sample Data

The database is pre-loaded with the Books table. Run the query below to explore the data.

Books Table

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: