Transcript

Let's say we have a users table: 

create table users (
  id serial primary key,
  first varchar not null,
  last varchar not null
);

And we have a handful of users in that table with first and last names: 

insert into users (first, last)
values ('Hank', 'Hooper'),
('Kathy', 'Geiss'),
('Devon', 'Banks'),
('Don', 'Geiss'),
('Jack', 'Donaghy');

We want to take a look at all of the users in our system, so we run a select command on the table for first and last name: 

select first, last from users;

Great, but the select statement is giving us the users in the order they were inserted. What we'd really like to do is see the list of users ordered by last name and then first name. We can do this by including an order by clause: 

select first, last
from users
order by last, first;

This is probably how we usually see the order by clause used. It is quite a bit more flexible than this though. Instead of naming the output columns directly, we can reference the indexes of the output columns.

Our first output column is first, so it gets an index of 1. Our second output column is last, so it gets an index of 2.

Let's use the indexes of our output columns to come up with a select statement identical to the previous one.

select first, last
from users
order by 2, 1;

As you might expect, the defaults for these orderings are ascending. We can change them to descending just as we would do with any other order by clause: 

select first, last
from users
order by 2 desc, 1 desc;

In these examples we aren't saving much. However, when we are constructing complex statements joining between many tables, being able to use an index proves to be very useful shortcut.

That's it for this episode. Thanks for watching.