Redshift First_Value and Last_Value Functions Made Easy

With the huge volumes of data being generated by enterprises today, businesses are looking for modernized means of data storage. On-premise storage options are associated with many limitations including lack of adequate scalability, poor accessibility, and high burden on maintenance.

That’s why organizations are moving their data from on-premise storage to the Cloud. This article discusses the Redshift first_value and last_value functions in detail. 

Amazon Redshift scales massively, allowing its users to store huge volumes of data. Redshift organizes data into tables. When using Redshift, you will want to run queries against a group of rows. This is possible using Redshift analytic functions, also called window functions.

The Redshift first_value and last_value functions are good examples of Redshift analytic functions. They allow you to run queries against a group of rows. Before getting into Redshift first_value and last_value functions, let’s discuss this robust platform in detail.

Understanding AWS Redshift

Amazon Redshift is a managed, petabyte-scale Cloud Data Warehouse platform that makes the larger part of the AWS Cloud platform. Amazon Redshift provides its users with a platform where they can store all their data and analyze it to extract deep business insights.

Traditionally, businesses had to make sales predictions and other forecasts manually. Amazon Redshift does the largest part of the work of analyzing the data to give you time to focus on something else. It also gives you an opportunity to analyze your business data using the latest predictive analytics. This way, you can make smart decisions that can drive the growth of your business. You can learn more about Amazon Redshift from the official documentation.

Redshift First_Value and Last_Value Functions

When you have a group of items in a table column or expression, you may need to determine the first and the last items after sorting them. The Redshift first_value and last_value analytic functions help you to find the first value and the last value in an expression or column or within a group of rows. The functions require you to specify the sort criteria to help in determining the first and the last values.

When you have a set of ordered rows, the Redshift first_value function will return the value of the specified expression in respect to the first row of the window frame. The Redshift last_value function on the other hand will return the value of the expression in respect to the last row in the frame. 

This means that both the Redshift first_value and last_value functions are window functions because they calculate an aggregate value on a group of rows. 

Syntax

The Redshift first_value and last_value functions take the following syntax:

FIRST_VALUE | LAST_VALUE
( exp [ IGNORE NULLS | RESPECT NULLS ] ) OVER
(
[ PARTITION BY exp_list ]
[ ORDER BY list frame_clause ]
)

The parameters in the above syntax are described below:

• exp: The target expression or column that the function will operate on. 
• IGNORE NULLS: When you use this option with the Redshift FIRST_VALUE function, the function will return the first value in the window frame that is not NULL (or NULL in case all the values are NULL). When you use this option with the LAST_VALUE function, the function will return the last value in the frame that is not NULL (or NULL in case all the values are NULL).
• RESPECT NULLS: This parameter indicates that Amazon Redshift should consider or include null values when determining the row to use. This parameter is used by default if you don’t use the IGNORE NULLS parameter in your query.
• OVER: This parameter helps you to specify the window clauses for the function in your query.
• PARTITION BY exp_list: This parameter specifies the window for the function using either one or more expressions.
• ORDER BY list: This parameter will sort the rows within every partition. If you don’t specify any PARTITION BY clause, ORDER BY will sort the entire table. Note that when you use the ORDER BY clause in your query, you must also use a frame_clause in the same query.

That is the syntax of the Redshift first_value and last_value functions. Note that these two functions can be applied to any Redshift-supported data type. The functions return the same data type as that of the expression.

Examples

To demonstrate how the Redshift first_value and last_value functions work, we will use the venue table of the TICKIT sample Redshift Database.

Note: TICKIT database is well-known to most Redshift users as it is provided by Redshift as a sample database. You can refer to the official site for more information.

This example will demonstrate how to find the seating capacity of every venue and order the results by capacity (high or low). The Redshift first_value function will be used to select the venue name that corresponds to the first row of the frame, which in this case will be the row with the highest number of seats.

We will partition the results by state, which means that when the value venuestate changes, a new first value will be selected. The window frame is unbounded, meaning that the query will select the same first value for every row in each partition:

select venuename, venuestate, venueseats,
first_value(venuename)
over(partition by venuestate
order by venueseats descending
rows between unbounded preceding and unbounded following)
from (select * from venue where venueseats >0)
order by venuestate;

For the state of California, the Qualcomm Stadium has the highest number of seats, meaning that its name will be the first value for the rows in the CA partition. 

Now that you know how to use the Redshift first_value window function, let us demonstrate how to use the Redshift last_value window function.

select venuename, venuestate, venueseats,
last_value(venuename)
over(partition by venuestate
order by venueseats descending
rows between unbounded preceding and unbounded following)
from (select * from venue where venueseats >0)
order by venuestate;

For the state of California, the Shoreline Amphitheatre has the lowest number of seats, hence, it will be returned for each row in the partition.

That is how the Redshift first_value and last_value window functions work, as simple as that.

Conclusion

This is what you’ve learned in this article.

• The Redshift first_value and last_value functions are window functions because they are used to calculate an aggregate value over a group of rows. 
• When the Redshift first_value function is used on an ordered set of rows, it returns the value of the specified expression with respect to the first row of the window frame. 
• The Redshift last_value function returns the value of the expression with respect to the last row in the frame. 
• When the IGNORE NULLS option is used with the Redshift first_value function, it will return the first value in the window frame that is not NULL (or NULL in case all the values are NULL). 
• When the IGNORE NULLS option is used with the last_value function, the function will return the last value in the frame that is not NULL (or NULL in case all the values are NULL).
• The RESPECT NULLS parameter indicates that Amazon Redshift should consider null values when determining the row to be used. It is used by default if you don’t specify the IGNORE NULLS parameter.

However, in businesses, extracting complex data from various sources can be a challenging task and this is where Hevo saves the day!

Hevo Data, with its strong integration with 150+ Sources & BI tools, allows you to not only export data from a source of your choice & load data in the destinations such as AWS Redshift, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. In short, Hevo can help you store your data securely in Redshift.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs!

Share your experience of working with Redshift first_value and last_value functions in the comments section below.