Senior

Question 9. Write a query that returns employees with the highest salary in their position. Initialization script: CREATE TABLE users ( fio TEXT NOT NULL, position TEXT, salary BIGINT NOT NULL ); INSERT INTO users(fio, position, salary) VALUES ('Ivanov', 'Tester', 100000), ('Gorokhov', 'Tester', 120000), ('Petrov', 'Tester', 140000), ('Sidorov', 'Tester', 100000), ('Kuznetsov', 'Tester', 120000), ('Mikhaylov', 'Tester', 150000), ('Antonov', 'Developer', 200000), ('Sukhoi', 'Developer', 100000), ('Mikoyan', 'Developer', 100000), ('Kapustin', 'Director', 300000), ('Astakhov', 'IT', 200000); Example result: Mikhaylov Tester 150000 Antonov Developer 200000 Kapustin Director 300000 Astakhov IT 200000

sobes.tech AI

Answer from AI

To obtain employees with the highest salary in each position, you can use the window function RANK() or a subquery with grouping. Here's an example query using a subquery:

SELECT u.fio, u.position, u.salary
FROM users u
JOIN (
    SELECT position, MAX(salary) AS max_salary
    FROM users
    GROUP BY position
) max_salaries ON u.position = max_salaries.position AND u.salary = max_salaries.max_salary;

This query first finds the maximum salary for each position, then selects all employees whose salary equals the maximum for their position.

The result will be:

Михайлов Тестировщик 150000
Антонов Разработчик 200000
Капустин Директор 300000
Астахов ИБ 200000

Previous Next