How LAST_VALUE works in SQL? Best LAST_VALUE examples

How LAST_VALUE works in SQL? Best LAST_VALUE examples

When working with SQL, especially when dealing with analytical queries, window functions become incredibly useful. One such function is LAST_VALUE(), which allows us to retrieve the last value from a specified window of data.

Understanding LAST_VALUE in SQL

The LAST_VALUE() function is a window function that returns the last value in an ordered set of rows within a window partition. It differs from aggregate functions like MAX() because it respects the order of the rows.

The syntax of LAST_VALUE() looks like this:

LAST_VALUE(column_name) OVER (
    PARTITION BY partition_expression
    ORDER BY order_expression
    ROWS BETWEEN frame_start AND frame_end
)

Breaking it down:

  • column_name: The column from which the last value is retrieved.
  • PARTITION BY: Divides the data into partitions (optional).
  • ORDER BY: Defines the order of rows within the partition.
  • Frame clauses (ROWS BETWEEN): Determine the range of rows considered.

Basic Example

Let’s use a simple employee salary dataset:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    name VARCHAR(50),
    salary INT
);

INSERT INTO employees (id, department, name, salary) VALUES
(1, 'IT', 'Alice', 5000),
(2, 'IT', 'Bob', 6000),
(3, 'IT', 'Charlie', 7000),
(4, 'HR', 'David', 5500),
(5, 'HR', 'Eve', 4500);

Now, using LAST_VALUE():

SELECT 
    id,
    department,
    name,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary
FROM employees;

This ensures that for each department, we get the salary of the last ordered employee.

Why Use LAST_VALUE Over MAX?

Unlike MAX(), which finds the overall maximum, LAST_VALUE() considers the order strictly. It provides:

  1. The ability to fetch the last value dynamically based on custom ordering.
  2. More flexibility compared to MAX() when dealing with ranked data.

Frame Clause Impact

By default, LAST_VALUE() only considers rows up to the current row. If we do not specify ROWS BETWEEN correctly, unexpected results can occur.

Example of an incorrect usage:

SELECT 
    id,
    department,
    name,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS last_salary
FROM employees;

Here, LAST_VALUE() will only consider rows up to the current row, leading to unintended results.

Using LAST_VALUE with Different Data Types

LAST_VALUE() is not limited to numeric values. It works with:

  • Dates – e.g., last login date.
  • Strings – e.g., last assigned task.
  • Boolean values – e.g., last known status.

Practical Use Cases

Some common scenarios for LAST_VALUE() include:

  • Fetching the last ordered item in online shopping databases.
  • Determining the last recorded salary in HR systems.
  • Tracking last login times for users.

LAST_VALUE vs FIRST_VALUE

Both LAST_VALUE() and FIRST_VALUE() are window functions but retrieve different results.

Function Result
FIRST_VALUE(column_name) Returns the first value in the window.
LAST_VALUE(column_name) Returns the last value in the window.

Common Pitfalls and Fixes

To avoid issues with LAST_VALUE(), consider:

  1. Specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when needing the true last value.
  2. Ensuring proper ORDER BY to get expected results.
  3. Understanding the difference between window functions and aggregate functions like MAX().

Conclusion

LAST_VALUE() is a powerful SQL function when used correctly. Unlike MAX(), it preserves row order and provides dynamic last values based on partitions. Whether analyzing salary data or tracking user actions, mastering LAST_VALUE() can greatly enhance query capabilities.

 

Other interesting article:

How FIRST_VALUE works in SQL? Best FIRST_VALUE examples