Search⌘ K
Home/
Courses/
SQL Interview Preparation—Intermediate Level

Answer: Using FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()

Find a detailed explanation of how to use FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() window functions in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to select specific values using SQL functions
SELECT SalesID, Month, SalesAmount,
        FIRST_VALUE(SalesAmount) OVER ( ORDER BY SalesID
          ) AS FirstMonthSales,
        LAST_VALUE(SalesAmount) OVER ( ORDER BY SalesID
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastMonthSales,
        NTH_VALUE(SalesAmount, 6) OVER ( ORDER BY SalesID
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS JuneSales
FROM   Sales;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT statement selects the columns SalesID, Month, and SalesAmount. We use AS to set an alias for each calculated column.

  • Lines 3–4: The FIRST_VALUE() function gets the sales from the first recorded SalesAmount based on the order of SalesID.

  • Lines 5–6: The LAST_VALUE() function gets the sales from the last recorded SalesAmount based on the order of SalesID. It considers the entire dataset with the ROWS clause set to UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

  • Lines 7–8: The NTH_VALUE() function retrieves the sales from the 6th recorded SalesAmount based on the order of SalesID. It considers the entire dataset with the ROWS clause set to UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

  • Line 9: The FROM clause specifies the table name as Sales. ...