gistfile1.txt

# Sorting Rows By Index

Welcome back to pgcasts, my name is Josh Branchaud. In this episode I am going to talk about a handy shortcut we can use when ordering rows in a select statement.

Let's say we have a users table:

```sql
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:

```sql
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:

```sql
select first, last from users;
```

Great. But what we'd really like to do is see that list of users ordered by last name and then first name. We can do this by including an `order by` clause:

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

The `order by` clause is actually quite flexible though. Instead of explicitly using the names of the output columns, we can instead reference the indexes of the output columns. If we list `first` as the first output column, and `last` as the second, then they have indexes of 1 and 2 respectively. The previous statement can be transformed into the following equivalent statement:

```sql
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;
```

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