Instruction
Great! So far, we've learned how indexes can help us quickly find rows to be returned by a query. However, they can also be used to deliver rows in a specific order.
We still have the same table, player:
CREATE TABLE player ( id integer PRIMARY KEY, first_name varchar(64), last_name varchar(64), year_born integer, country varchar(64), current_points integer );
We previously created a multi-column index on the columns country and current_points. However, we quickly realized that we typically list players by current_points in descending order. We can create an index in the following way:
CREATE INDEX player_multi_country_points_index ON player(country ASC, current_points DESC);
Note the words ASC and DESC after the column names. They indicate the order in which rows should be sorted in the index. ASC is the default option – it'll be automatically used by the database if you don't explicitly specify DESC. The difference is shown in the picture below:
Exercise
We previously created the following index:
CREATE INDEX city_rating_index ON driver(city, rating);
Modify it so that the cities are indexed in ascending order while the ratings are indexed in descending order.
Stuck? Here's a hint!
Write ASC after city and DESC after rating.
