In this episode we will look at a handy shortcut to use when ordering rows in a select statement.
Postgres Version 9.5
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
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
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.