Postgres Conditionals: How to Use Greatest and Least
How to use the "greatest" and "least" statements in PostgreSQL. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
Hey everyone, today we're going to look at the "greatest" and "least" statements in Postgres.
---
"Greatest" and "least" are conditional statements in Postgres that accept a list of arguments, returning the largest or smallest value from the arguments.
Let's take a look at how this works.
For this example, I have a table called users with a few entries.
```
\d users
```
```sql
select * from users;
```
From inspecting the table, you'll notice that we have a few date columns on our users, sign up date, last post date and last comment date.
If we wanted to read from our users table and find something like a last activity date, we can use "greatest," passing it each of our date columns.
```sql
select name, greatest(sign_up_date, last_post_date, last_comment_date) as last_activity_date from users;
```
You'll notice that all of our users have a last activity date, even though our last user, named "Dana" didn't have a value for last comment date. This is because the "greatest" and "least" statements ignore null values in their argument lists. The methods will only return null if all of the values in the argument list are null.
We can see this if we create a new user, and then execute our same select statement from earlier.
```sql
insert into users (name) values ('Ed');
```
```sql
select name, greatest(sign_up_date, last_post_date, last_comment_date) as last_activity_date from users;
```
The "least" command works in the exact same way as "greatest", accepting a list of values and returning the smallest of the values. In the case of our data, we can do something silly like select the earliest activity date from our users using "least" and passing it our date columns.
```sql
select least(sign_up_date, last_comment_date, last_post_date) as earliest_activity_date from users;
```
It's important to note that the greatest and least commands are very different from the max and min aggregate commands in Postgres. Greatest and least compute a different value for each row being read out, and accept a list of values. Max and min compute a single result based on all the values from the rows being read out, and only accept a single argument.
So using max with last post date as latest post from our users reads in the last post date from each one of our user rows, and returns the greatest of all the last post dates from all of our users.
```sql
select max(last_post_date) as latest_post from users;
```
This is very different from using greatest with last post date and last comment date to find the latest post from our users. Greatest returns the latest post _per user_.
```sql
select name, greatest(last_post_date, last_comment_date) as latest_post from users;
```
Thanks for watching!
Watch video Postgres Conditionals: How to Use Greatest and Least online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 14 November 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 672 once and liked it 14 people.