Using FIRST_VALUE and LAST_VALUE to Analyze Salaries

Use FIRST_VALUE and LAST_VALUE Functions

Write a SQL query to find the highest and lowest salaries within each department using FIRST_VALUE and LAST_VALUE.

Solution:

-- Find the highest and lowest salaries within each department.
SELECT DISTINCT
    DepartmentID,
    FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS HighestSalary,
    LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary ASC 
                             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LowestSalary
FROM Employees;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use FIRST_VALUE and LAST_VALUE to find the highest and lowest salaries within each department.

2. Key Components :

1. FIRST_VALUE(Salary) : Retrieves the first value in the ordered set (highest salary when ordered descending).
2. LAST_VALUE(Salary) : Retrieves the last value in the ordered set (lowest salary when ordered ascending).
3. PARTITION BY DepartmentID : Groups data by department.
4. ROWS BETWEEN Clause : Ensures LAST_VALUE considers the entire partition.

3. Why use FIRST_VALUE and LAST_VALUE? :

1. These functions simplify retrieving specific values from a window of rows.

4. Real-World Application :

1. For example, in HR systems, you might use this query to identify salary extremes within departments.

Additional Notes:

• The ROWS BETWEEN clause is critical for LAST_VALUE to work correctly.
• Use this exercise to teach how to retrieve specific values from a windowed dataset.

For more Practice: Solve these Related Problems:

• Write a SQL query to find the highest and lowest salaries within each job title using FIRST_VALUE and LAST_VALUE.
• Write a SQL query to retrieve the top-performing employee and the least-performing employee in each team based on sales figures.
• Write a SQL query to analyze the range of salaries (highest minus lowest) within each department.
• Write a SQL query to determine the best and worst scores achieved by students in each subject.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Create a Recursive CTE for Hierarchical Data.
Next SQL Exercise: Parse and Modify JSON Data.