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.
Смотрите видео How To Order Rows By Index in PostgreSQL онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь PG Casts by Hashrocket 22 Август 2016, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 653 раз и оно понравилось 12 людям.