SQL 59 🛢️ Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE - KandZ

The window functions so far access the adjacent rows and the ranked positions. The boundary functions—FIRST_VALUE, LAST_VALUE, and NTH_VALUE—access the specific rows at the edges of the window or at the arbitrary position. They answer the questions “what was the first value?” and “what was the last value?” and “what was the third value?” The frame clause is the crucial difference: the LAST_VALUE with the default frame returns the current row, not the last row, and the misunderstanding of the frame is the source of the most common bug in the window functions.

Key point: FIRST_VALUE(column) returns the value of the column from the first row of the window frame. LAST_VALUE(column) returns the value from the last row of the frame. NTH_VALUE(column, n) returns the value from the nth row of the frame. The frame determines which rows are included. The default frame with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means the LAST_VALUE returns the current row’s value, not the partition’s last row. The full frame requires the explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Why the boundary functions exist

The first and last problem. The report needs the first order date and the last order date for each customer, shown on every row. The FIRST_VALUE and the LAST_VALUE are the tools. The MIN and the MAX also work for the dates, but the boundary functions work for the non-orderable values: the first product name, the last comment text, the arbitrary label.

The frame problem. The FIRST_VALUE is straightforward: the first row of the frame is the first row of the partition regardless of the frame, because the frame starts at the UNBOUNDED PRECEDING. The LAST_VALUE is not: the default frame stops at the current row, and the “last value” is the current row’s value. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is the requirement for the partition’s last row.

The nth problem. The NTH_VALUE accesses the arbitrary position in the frame. The NTH_VALUE(price, 2) is the second price in the window. The NTH_VALUE is the tool for the specific position that is not the first or the last.

The self-join problem. Before the window functions, the first and the last required the self-join on the min and the max. The boundary functions express the same without the join.

The ordering problem. The FIRST_VALUE and the LAST_VALUE depend on the ORDER BY inside the OVER clause. The order determines which row is the first and which is the last. The wrong order produces the wrong first and last.

The performance problem. The boundary functions are the window functions, and the performance is the same as the other window functions. The alternative—the subquery with the MIN and the MAX, or the self-join—is the different plan, and the boundary functions are often the simpler and the faster.

a. The FIRST_VALUE

The FIRST_VALUE(column) returns the value from the first row of the frame.

SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS first_order
FROM orders;

The first_order is the earliest order date for the customer, shown on every row. The first row of the partition, and the value is the first order’s date.

The FIRST_VALUE with the different column.

SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS first_amount
FROM orders;

The first_amount is the amount of the customer’s first order. The pattern is for the comparison with the first value.

The FIRST_VALUE with the descending order.

SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC
    ) AS highest_amount
FROM orders;

The first_amount is the amount of the customer’s highest order, because the ORDER BY amount DESC puts the highest first. The FIRST_VALUE with the descending order is the same as the MAX for the amount.

The FIRST_VALUE with the frame.

SELECT
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS two_rows_back
FROM orders;

The frame is the current row and the two rows before. The FIRST_VALUE is the value from the two rows back, which is the same as the LAG(amount, 2).

b. The LAST_VALUE and the frame

The LAST_VALUE(column) returns the value from the last row of the frame. The default frame is the source of the bug.

SELECT
    customer_id,
    order_date,
    amount,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS last_order
FROM orders;

The last_order is the current row’s order date, not the customer’s last order date. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means the frame’s last row is the current row.

The fix is the explicit full frame.

SELECT
    customer_id,
    order_date,
    amount,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

The last_order is the customer’s last order date, shown on every row. The ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING includes the whole partition.

The LAST_VALUE without the ORDER BY.

SELECT
    customer_id,
    order_date,
    amount,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id
    ) AS last_order
FROM orders;

The last_order is the arbitrary row’s date, because the frame without the ORDER BY is the whole partition, and the “last” is the non-deterministic. The ORDER BY is the requirement for the deterministic result.

The LAST_VALUE with the bounded frame.

SELECT
    order_date,
    amount,
    LAST_VALUE(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS two_rows_ahead
FROM orders;

The frame is the current row and the two rows after. The LAST_VALUE is the value from the two rows ahead, which is the same as the LEAD(amount, 2).

c. The NTH_VALUE

The NTH_VALUE(column, n) returns the value from the nth row of the frame.

SELECT
    customer_id,
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_amount
FROM orders;

The second_amount is the amount of the customer’s second order, shown on every row. The n is 2.

The NTH_VALUE from the end.

SELECT
    customer_id,
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_to_last_amount
FROM orders;

The second_to_last_amount is the amount of the customer’s second-to-last order, because the ORDER BY DESC reverses the order. The NTH_VALUE with the descending order is the tool for the “second from the end.”

The NTH_VALUE with the default frame.

SELECT
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        ORDER BY order_date
    ) AS second_so_far
FROM orders;

The default frame is the current row and the rows before. The NTH_VALUE(amount, 2) is the second amount so far, if the current row is the second or later. The first row has the NULL because the second row is not in the frame.

The NTH_VALUE with the frame of the specific size.

SELECT
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS second_in_window
FROM orders;

The frame is the current row and the one row before and after. The NTH_VALUE(amount, 2) is the second value in the three-row window, which is the current row.

The comparison of the boundary functions.

Function	Returns
`FIRST_VALUE(col)`	The first value in the frame
`LAST_VALUE(col)`	The last value in the frame
`NTH_VALUE(col, n)`	The nth value in the frame
`LAG(col, n)`	The value n rows back
`LEAD(col, n)`	The value n rows forward
`MIN(col)`	The minimum
`MAX(col)`	The maximum

The FIRST_VALUE and the LAG are different: the LAG is the n rows back from the current, and the FIRST_VALUE is the first in the frame. The LAST_VALUE and the LEAD are the same difference.

The use cases.

Use Case	Function
The first order per customer	`FIRST_VALUE`
The last order per customer	`LAST_VALUE` + the full frame
The first product in the category	`FIRST_VALUE`
The second highest	`NTH_VALUE` or `DENSE_RANK`
The previous value	`LAG`
The next value	`LEAD`
The running minimum	`MIN` OVER

The NTH_VALUE versus the DENSE_RANK.

-- The second highest per category with the NTH_VALUE
SELECT
    product_id,
    category,
    price,
    NTH_VALUE(price, 2) OVER (
        PARTITION BY category
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM products;

The second_highest is the second-highest price in the category, shown on every row. The NTH_VALUE is the tool for the value.

-- The rows with the second highest price with the DENSE_RANK
SELECT * FROM (
    SELECT
        product_id,
        category,
        price,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
    FROM products
) AS ranked
WHERE rnk = 2;

The DENSE_RANK is the tool for the rows. The NTH_VALUE gives the value, and the DENSE_RANK gives the rows. The choice depends on what the query should return.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
    (1, 1, '2026-01-01', 100),
    (2, 1, '2026-01-15', 150),
    (3, 1, '2026-02-01', 200),
    (4, 2, '2026-01-05', 50),
    (5, 2, '2026-01-20', 75);

-- ============================================
-- PART 2: FIRST_VALUE
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS first_order
FROM orders;

-- ============================================
-- PART 3: LAST_VALUE BUG (DEFAULT FRAME)
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS last_order
FROM orders;
-- Returns the current row's date, not the last

-- ============================================
-- PART 4: LAST_VALUE FIX (FULL FRAME)
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    LAST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

-- ============================================
-- PART 5: NTH_VALUE
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_amount
FROM orders;

-- ============================================
-- PART 6: NTH_VALUE FROM THE END
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_to_last
FROM orders;

-- ============================================
-- PART 7: FIRST AND LAST TOGETHER
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(order_date) OVER w AS first_order,
    LAST_VALUE(order_date) OVER w AS last_order
FROM orders
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

-- ============================================
-- PART 8: FIRST_VALUE WITH THE DESCENDING ORDER
-- ============================================
SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC
    ) AS highest_amount
FROM orders;

-- ============================================
-- PART 9: NTH_VALUE WITH THE BOUNDED FRAME
-- ============================================
SELECT
    order_date,
    amount,
    NTH_VALUE(amount, 2) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS second_in_window
FROM orders;

-- ============================================
-- PART 10: SECOND HIGHEST VALUE
-- ============================================
SELECT * FROM (
    SELECT
        product_id,
        category,
        price,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
    FROM products
) AS ranked
WHERE rnk = 2;

The ten parts covered the sample data, the FIRST_VALUE, the LAST_VALUE bug, the LAST_VALUE fix, the NTH_VALUE, the NTH_VALUE from the end, the FIRST_VALUE and the LAST_VALUE together, the FIRST_VALUE with the descending order, the NTH_VALUE with the bounded frame, and the second highest with the DENSE_RANK.

Quick Reference

The Boundary Functions

Function	Returns
`FIRST_VALUE(col)`	The first value in the frame
`LAST_VALUE(col)`	The last value in the frame
`NTH_VALUE(col, n)`	The nth value in the frame

The Frame

Frame	`LAST_VALUE` Returns
Default (with `ORDER BY`)	The current row
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	The partition’s last
`ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING`	The two rows ahead

The Comparison

Function	Direction
`FIRST_VALUE`	The frame start
`LAST_VALUE`	The frame end
`NTH_VALUE(col, n)`	The nth in the frame
`LAG(col, n)`	n rows back
`LEAD(col, n)`	n rows forward

The Use Cases

Use Case	Function
The first order per customer	`FIRST_VALUE`
The last order per customer	`LAST_VALUE` + the full frame
The second highest value	`NTH_VALUE`
The first product in the category	`FIRST_VALUE`
The previous value	`LAG`
The next value	`LEAD`

Best Practices

✅ Do This:

-- Use the full frame for the LAST_VALUE
LAST_VALUE(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)                                                              -- ✅

-- Use the named window for the multiple functions
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)                                                              -- ✅

-- Use the ORDER BY for the deterministic FIRST_VALUE
FIRST_VALUE(amount) OVER (PARTITION BY c ORDER BY date)        -- ✅

-- Use the NTH_VALUE for the specific position
NTH_VALUE(price, 2) OVER (PARTITION BY c ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  -- ✅

-- Use the DENSE_RANK for the rows with the nth value
DENSE_RANK() OVER (PARTITION BY c ORDER BY price DESC) = 2     -- ✅

❌ Don’t Do This:

-- Don't use the LAST_VALUE with the default frame
LAST_VALUE(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
)  -- returns the current row, not the last                    -- ❌

-- Don't use the boundary functions without the ORDER BY
FIRST_VALUE(amount) OVER (PARTITION BY c)  -- non-deterministic -- ⚠️

-- Don't use the NTH_VALUE with the n beyond the frame
NTH_VALUE(amount, 10) OVER (
    ORDER BY date ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
)  -- returns NULL                                              -- ⚠️

-- Don't confuse the FIRST_VALUE with the LAG
FIRST_VALUE(amount) OVER (ORDER BY date)  -- the first, not the previous -- ⚠️

-- Don't use the boundary functions in the WHERE
WHERE LAST_VALUE(...) OVER (...) = 1  -- error                  -- ❌

-- Don't forget the frame for the LAST_VALUE in the running context
-- The default frame stops at the current row                   -- ⚠️

Common Pitfalls

Pitfall	Why It Happens	Fix
`LAST_VALUE` returns the current	The default frame	Add the full frame
`FIRST_VALUE` non-deterministic	No `ORDER BY`	Add the `ORDER BY`
`NTH_VALUE` returns the `NULL`	The n beyond the frame	Check the frame
The wrong first or last	The `ORDER BY` reversed	Check the order
Window function in `WHERE`	Not allowed	Wrap in the subquery
The frame confusion	The default is running	Use the explicit frame

Real-World Examples

1. The First Order per Customer

SELECT customer_id, order_date,
       FIRST_VALUE(order_date) OVER (
           PARTITION BY customer_id ORDER BY order_date
       ) AS first_order
FROM orders;

2. The Last Order per Customer

SELECT customer_id, order_date,
       LAST_VALUE(order_date) OVER (
           PARTITION BY customer_id ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_order
FROM orders;

3. The First and the Last

SELECT customer_id, order_date,
       FIRST_VALUE(order_date) OVER w AS first_order,
       LAST_VALUE(order_date) OVER w AS last_order
FROM orders
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

4. The Second Highest Value

SELECT product_id, price,
       NTH_VALUE(price, 2) OVER (
           PARTITION BY category ORDER BY price DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_highest
FROM products;

5. The Highest per Group

SELECT product_id, price,
       FIRST_VALUE(price) OVER (
           PARTITION BY category ORDER BY price DESC
       ) AS highest
FROM products;

6. The First Value in the Category

SELECT product_id, name,
       FIRST_VALUE(name) OVER (
           PARTITION BY category ORDER BY name
       ) AS first_name
FROM products;

7. The Second-to-Last Order

SELECT customer_id, order_date,
       NTH_VALUE(order_date, 2) OVER (
           PARTITION BY customer_id ORDER BY order_date DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_to_last
FROM orders;

8. The Running First

SELECT order_date, amount,
       FIRST_VALUE(amount) OVER (ORDER BY order_date) AS first_so_far
FROM orders;

9. The Windowed Nth

SELECT order_date, amount,
       NTH_VALUE(amount, 2) OVER (
           ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS second_in_window
FROM orders;

10. The Earliest and Latest

SELECT customer_id,
       FIRST_VALUE(order_date) OVER w AS earliest,
       LAST_VALUE(order_date) OVER w AS latest
FROM orders
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

Visual

FIRST_VALUE and LAST_VALUE

┌─────────────────────────────────────────────────────────────┐
│  PARTITION BY customer_id ORDER BY order_date               │
│  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   │
│                                                             │
│  Customer 1:                                                │
│  ┌────────────┬────────┬────────┬────────┐                  │
│  │ order_date │ amount │ FIRST  │ LAST   │                  │
│  ├────────────┼────────┼────────┼────────┤                  │
│  │ 2026-01-01 │ 100    │ 01-01  │ 02-01  │                  │
│  │ 2026-01-15 │ 150    │ 01-01  │ 02-01  │                  │
│  │ 2026-02-01 │ 200    │ 01-01  │ 02-01  │                  │
│  └────────────┴────────┴────────┴────────┘                  │
│                                                             │
│  The FIRST_VALUE and the LAST_VALUE are the same on every   │
│  row of the partition.                                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The LAST_VALUE Bug

┌─────────────────────────────────────────────────────────────┐
│  DEFAULT FRAME: RANGE BETWEEN UNBOUNDED PRECEDING           │
│  AND CURRENT ROW                                            │
│                                                             │
│  ┌────────────┬────────┬──────────┐                         │
│  │ order_date │ amount │ LAST     │                         │
│  ├────────────┼────────┼──────────┤                         │
│  │ 2026-01-01 │ 100    │ 01-01    │  ← the current row      │
│  │ 2026-01-15 │ 150    │ 01-15    │  ← the current row      │
│  │ 2026-02-01 │ 200    │ 02-01    │  ← the current row      │
│  └────────────┴────────┴──────────┘                         │
│                                                             │
│  The "last value" is the current row's value.               │
│  Not the partition's last row.                              │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  FULL FRAME: ROWS BETWEEN UNBOUNDED PRECEDING               │
│  AND UNBOUNDED FOLLOWING                                    │
│                                                             │
│  ┌────────────┬────────┬──────────┐                         │
│  │ order_date │ amount │ LAST     │                         │
│  ├────────────┼────────┼──────────┤                         │
│  │ 2026-01-01 │ 100    │ 02-01    │  ← the partition's last │
│  │ 2026-01-15 │ 150    │ 02-01    │  ← the partition's last │
│  │ 2026-02-01 │ 200    │ 02-01    │  ← the partition's last │
│  └────────────┴────────┴──────────┘                         │
│                                                             │
│  The explicit full frame is the fix.                        │
│                                                             │
└─────────────────────────────────────────────────────────────┘

NTH_VALUE

┌─────────────────────────────────────────────────────────────┐
│  NTH_VALUE(amount, 2) OVER (                                │
│    PARTITION BY customer_id ORDER BY order_date             │
│    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING │
│  )                                                          │
│                                                             │
│  ┌────────────┬────────┬──────────┐                         │
│  │ order_date │ amount │ NTH(2)   │                         │
│  ├────────────┼────────┼──────────┤                         │
│  │ 2026-01-01 │ 100    │ 150      │  ← the second amount    │
│  │ 2026-01-15 │ 150    │ 150      │  ← the second amount    │
│  │ 2026-02-01 │ 200    │ 150      │  ← the second amount    │
│  └────────────┴────────┴──────────┘                         │
│                                                             │
│  The NTH_VALUE is the same on every row of the partition.   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The Boundary Functions Compared

┌─────────────────────────────────────────────────────────────┐
│  PARTITION BY customer ORDER BY date                        │
│                                                             │
│  ┌────────────┬────────┬──────┬──────┬──────────┐           │
│  │ date       │ amount │ LAG  │ LEAD │ FIRST    │           │
│  ├────────────┼────────┼──────┼──────┼──────────┤           │
│  │ 2026-01-01 │ 100    │ NULL │ 150  │ 100      │           │
│  │ 2026-01-15 │ 150    │ 100  │ 200  │ 100      │           │
│  │ 2026-02-01 │ 200    │ 150  │ NULL │ 100      │           │
│  └────────────┴────────┴──────┴──────┴──────────┘           │
│                                                             │
│  The LAG and the LEAD are the adjacent rows.                │
│  The FIRST_VALUE is the frame's first row.                  │
│  The LAST_VALUE with the full frame is the frame's last.    │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
`FIRST_VALUE(col)`	The first in the frame
`LAST_VALUE(col)`	The last in the frame
`NTH_VALUE(col, n)`	The nth in the frame
Default frame	`UNBOUNDED PRECEDING AND CURRENT ROW`
Full frame	`UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
`LAST_VALUE` bug	The default returns the current
`ORDER BY`	Required
`PARTITION BY`	Optional, resets
The filter	Subquery + outer `WHERE`
The named window	`WINDOW w AS (...)`

Key takeaways:

The FIRST_VALUE returns the first row of the frame. The first row of the partition is the frame’s start, and the FIRST_VALUE is the same on every row of the partition. The result is the deterministic with the ORDER BY.
The LAST_VALUE returns the last row of the frame. The default frame stops at the current row, and the LAST_VALUE is the current row’s value. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is the fix.
The NTH_VALUE returns the nth row of the frame. The n is the position, and the NTH_VALUE is the tool for the specific position that is not the first or the last. The frame determines which rows are available.
The frame is the crucial difference. The FIRST_VALUE is straightforward because the frame starts at the UNBOUNDED PRECEDING. The LAST_VALUE is the trap because the default frame stops at the current row. The explicit full frame is the requirement.
The ORDER BY is the requirement. The FIRST_VALUE, the LAST_VALUE, and the NTH_VALUE depend on the order. The ORDER BY inside the OVER clause defines the order, and the meaningful result requires the correct order.
The boundary functions cannot be used in the WHERE. The window function is evaluated after the WHERE. The filter requires the subquery: the inner query computes the boundary, and the outer query filters.
The NTH_VALUE and the DENSE_RANK are different tools. The NTH_VALUE returns the value from the nth row, and the DENSE_RANK returns the rows with the nth rank. The choice depends on what the query should return: the value or the rows.

Remember: The boundary functions access the edges of the window. The FIRST_VALUE is the start, the LAST_VALUE is the end, and the NTH_VALUE is the arbitrary position. The frame determines which rows are included, and the LAST_VALUE with the default frame is the trap. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is the fix. The ORDER BY is the requirement, and the partition resets the sequence. The boundary functions are the tools for the first, the last, and the nth, and the frame is the detail that makes the result correct.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!