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
Find Latest Salaries INPUT Salary Table emp_id name salary dept_id 1 John 50000 101 1 John 60000 101 2 Jane 45000 102 2 Jane 55000 102 3 Bob 70000 101 = Latest (Highest) Salary Multiple records per employee (old + new) E1 E2 E3 2 records 2 records 1 record ALGORITHM STEPS 1 GROUP BY emp_id Group all salary records by employee ID 2 Find MAX(salary) For each group, get the highest salary (=latest) 3 JOIN with Original Join back to get full employee details 4 ORDER BY emp_id Sort results in ascending order by employee ID SELECT emp_id, firstname, lastname, salary, dept_id FROM Salary WHERE (emp_id,salary) IN (MAX subquery) FINAL RESULT Current Salaries emp_id name salary dept 1 John 60000 101 2 Jane 55000 102 3 Bob 70000 101 OK - Done! Only ONE record per employee with their LATEST (highest) salary BEFORE 5 rows --> AFTER 3 rows Ordered by emp_id ASC Key Insight: Since salaries increase each year, the HIGHEST salary for each employee represents their CURRENT (most recent) salary. Use GROUP BY with MAX() aggregation to find the latest salary, then JOIN back to get complete employee details. Alternative: Use ROW_NUMBER() window function with PARTITION BY emp_id ORDER BY salary DESC, then filter WHERE rn = 1. TutorialsPoint - Find Latest Salaries | Optimal Solution

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