Find Latest Salaries - Problem

You are given a Salary table that contains employee information and their yearly salaries. However, some records are outdated and contain old salary information.

Write a solution to find the current salary of each employee, assuming that salaries increase each year (higher salary = more recent).

Return the result table with emp_id, firstname, lastname, salary, and department_id, ordered by emp_id in ascending order.

Table Schema

Salary
Column Name Type Description
emp_id PK int Employee ID
firstname varchar Employee first name
lastname varchar Employee last name
salary PK varchar Employee yearly salary
department_id varchar Department identifier
Primary Key: (emp_id, salary)
Note: Each employee may have multiple salary records, with higher salary indicating more recent data

Input & Output

Example 1 — Multiple Salary Records
Input Table:
emp_id firstname lastname salary department_id
1 John Doe 50000 IT
1 John Doe 75000 IT
2 Jane Smith 60000 HR
2 Jane Smith 80000 HR
3 Bob Wilson 45000 Finance
Output:
emp_id firstname lastname salary department_id
1 John Doe 75000 IT
2 Jane Smith 80000 HR
3 Bob Wilson 45000 Finance
💡 Note:

John has two salary records (50000, 75000), so we select the higher one (75000). Jane has two records (60000, 80000), so we select 80000. Bob has only one record, so we keep 45000.
Example 2 — Single Records Only
Input Table:
emp_id firstname lastname salary department_id
1 Alice Johnson 95000 Engineering
2 Charlie Brown 72000 Marketing
Output:
emp_id firstname lastname salary department_id
1 Alice Johnson 95000 Engineering
2 Charlie Brown 72000 Marketing
💡 Note:

When each employee has only one salary record, all records are returned as they represent the latest (and only) salary for each employee.

Constraints

  • 1 ≤ emp_id ≤ 1000
  • salary is stored as varchar but represents numeric values
  • firstname and lastname are non-empty strings
  • department_id is a valid department identifier

Visualization

Tap to expand
SQL Problem: Find Latest SalariesInput: Multiple Salary Recordsemp_idsalary150000175000280000ROW_NUMBER()Keep MaxOutput: Latest Salaries Onlyemp_idsalary175000280000Old salary record (50000) filtered out
Understanding the Visualization
1
Input
Salary table with multiple records per employee
2
Rank
ROW_NUMBER() partitioned by emp_id
3
Filter
Keep only rank 1 (highest salary)
Key Takeaway
🎯 Key Insight: Use window functions to efficiently find the maximum value per group

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18
23.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen