Question 9. Write a query that returns employees with the highest salary in their position. Go to onecompiler.com/postgresql 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), ('Kapitulin', 'Director', 300000), ('Astakhov', 'IT', 200000); Example result: Mikhaylov Tester 150000 Antonov Developer 200000 Kapitulin 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 the aggregate function MAX().

Example with a subquery:

SELECT fio, position, salary
FROM users u
WHERE salary = (
    SELECT MAX(salary)
    FROM users
    WHERE position = u.position
);

This query compares each row's salary with the maximum salary for that position and returns only those employees whose salary is the highest in their position.

Previous Next