How To Order Rows By Index in PostgreSQL
We look at a handy shortcut to use when ordering rows in a select statement. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
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 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:
```sql
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.
```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:
```sql
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.
Watch video How To Order Rows By Index in PostgreSQL online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 22 August 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 653 once and liked it 12 people.