Styjun

I have a table worker with columns id, name, salary, division_id and I need to display the workers with the top 3 salaries in each division. Here is sample data for table worker:

insert into worker values (1, 'Joe', 70000, 1);
insert into worker values (2, 'Henry', 80000, 2);
insert into worker values (3, 'Sam', 60000, 2);
insert into worker values (4, 'Max', 90000, 1);
insert into worker values (5, 'Janet', 69000, 1);
insert into worker values (6, 'Randy', 85000, 1);
insert into worker values (7, 'Jordan', 70000, 4);
insert into worker values (8, 'Adam', 69000, 3);
insert into worker values (9, 'David', 76000, 4);
insert into worker values (10, 'Moses', 68000, 4);
insert into worker values (11, 'Solomon', 55000, 4);
insert into worker values (12, 'Cloe', 38000, 3);
insert into worker values (13, 'Sarah', 88000, 3);
insert into worker values (14, 'Deb', 92000, 3);
insert into worker values (15, 'Lea', 98000, 4);
commit;

For the above sample data my query should return the following rows:

Note: division 2 has only 2 rows because it has only 2 rows in sample data.

The query should be in Oracle or PostgreSQL.

I know I can limit the number of rows with Oracle's ROWNUM or Postgres LIMIT keyword, but it doesn't help me here as I need to retrieve 3 rows or less for each division_id.

My query should be efficient as possible because the number of rows in workers can be huge (I don't know the exact row count).

In my solution, I am using a CURSOR and FOR my_rec IN my_cursor to scan the result-set and print only the first 3 rows or fewer for every division_id. This solution gives me the complexity of O(N), and I am hoping to find a better solution, maybe solve it with a single query without the use of CURSOR.

DECLARE
 CREATE CURSOR my_cursor IS
 SELECT division_id, name AS worker, salary
 FROM worker
 ORDER BY division_id, salary desc;
 division_id NUMBER;
 row_count_per_id NUMBER;
BEGIN
 FOR my_rec IN my_cursor LOOP
 -- If first iteration then initialize variables
 IF (row_num = 0) THEN
 division_id := myRec.division_id;
 row_count_per_id := 1;
 END IF;

 -- row_num can't be 0 at this point
 IF (division_id = myRec.division_id) THEN
 IF (row_count_per_id < 3) THEN
 -- Print first record of the new division_id
 DBMS_OUTPUT.PUT_LINE('division_id = ' || myRec.division_id || 
 ', Worker = ' myRec.worker ||
 ', salary = ' myRec.salary;
 row_count_per_id := row_count_per_id + 1;
 END IF;
 ELSE
 -- division_id has changed from the previous row
 -- Add the first row of new division_id 
 division_id = myRec.division_id;
 row_count_per_id := 1;
 DBMS_OUTPUT.PUT_LINE('division_id = ' || myRec.division_id || 
 ', Worker = ' myRec.worker ||
 ', salary = ' myRec.salary;
 END IF;
 END LOOP;
END;

Simply use a query with row_number

WITH c AS
(
 SELECT *,
 ROW_NUMBER() OVER (PARTITION BY division_id ORDER BY Salary DESC) AS rn
 FROM worker
)
SELECT *
 FROM c
WHERE rn <= 3

Depending on whether you want salaries that are equal receive the same rank and
be given sequential rank or otherwise, you may use RANK or DENSE_RANK in place of ROW_NUMBER