SQL FIRST_VALUE/LAST_VALUE Tutorial 🎯

Welcome to our SQL FIRST_VALUE and LAST_VALUE tutorial! In this comprehensive guide, we'll walk you through these powerful SQL functions, helping you understand why they're useful and how to use them effectively.

Understanding FIRST_VALUE and LAST_VALUE 📝

SQL's FIRST_VALUE and LAST_VALUE functions are used to retrieve the first or last value in a window, based on an order specified by you. These functions are particularly useful when dealing with sequential data where you want to reference rows that precede or follow the current row.

FIRST_VALUE 💡

The FIRST_VALUE function returns the first value in a window from the beginning of the result set, moving forward. It's handy when you want to access a value from an earlier row in your data.

LAST_VALUE 💡

The LAST_VALUE function, on the other hand, returns the last value in a window, moving backward from the current row. This function is useful when you want to access a value from a later row in your data.

Syntax 📝

The syntax for both functions is similar:

sql

FIRST_VALUE(column_name) OVER (ORDER BY column_order)
LAST_VALUE(column_name) OVER (ORDER BY column_order ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

column_name: The name of the column you want to retrieve the first or last value from.
column_order: The column or expression used to sort the rows in the window.
UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING: These keywords specify the starting and ending points of the window, respectively.

Practical Example 💡

Let's consider a simple table of sales transactions:

sql

CREATE TABLE sales (
    id INT,
    product VARCHAR(100),
    price DECIMAL(5,2),
    sale_date DATE
);

INSERT INTO sales VALUES
    (1, 'Product A', 100.00, '2022-01-01'),
    (2, 'Product B', 150.00, '2022-01-02'),
    (3, 'Product C', 75.00, '2022-01-03'),
    (4, 'Product D', 200.00, '2022-01-04'),
    (5, 'Product A', 90.00, '2022-01-05');

Now, let's find the first and last price of each product in our sales table:

sql

SELECT id, product, FIRST_VALUE(price) OVER (PARTITION BY product ORDER BY sale_date) AS first_price,
       LAST_VALUE(price) OVER (PARTITION BY product ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
FROM sales;

Output:

id | product | first_price | last_price
--:|:--------|-------------|-------------
 1 | Product A|     100.00   |     90.00
 2 | Product B|     150.00   |     150.00
 3 | Product C|      75.00   |      75.00
 4 | Product D|     200.00   |     200.00

In this example, we've partitioned our data by product and ordered it by sale date. For each product, we've found the first and last price using FIRST_VALUE and LAST_VALUE, respectively.

SQL FIRST_VALUE/LAST_VALUE Tutorial 🎯

SQL FIRST_VALUE/LAST_VALUE Tutorial 🎯

Understanding FIRST_VALUE and LAST_VALUE 📝

FIRST_VALUE 💡

LAST_VALUE 💡

Syntax 📝

Practical Example 💡

Quiz 💡