Martial,
I'm not sure why your first solution-environment choice was PL/SQL. I believe that the best solution environment for your needs is "plain" SQL with the addition of Oracle extremely powerful Analytic Function, "RANK()".
Thank you for posting the code to "CREATE TABLE PRINPORT_90...". But since we didn't get the code to create the tables from which you are creating PRINPORT_90, I won't be able to tailor a solution to your exact needs. Instead, I'll post code that should be similar to what you can use to solve your need.
First, here is a straight SELECT of the 25 rows in an employee table, EMP:
Code:
select dept_id, id, last_name, salary
from emp;
DEPT_ID ID LAST_NAME SALARY
-------- --- ------------------- -------
50 1 Velasquez 2,500
41 2 Ngao 1,450
31 3 Nagayama 1,400
10 4 Quick-To-See 1,450
50 5 Ropeburn 1,550
41 6 Urguhart 1,200
42 7 Menchu 1,250
43 8 Biri 1,100
44 9 Catchpole 1,300
45 10 Havel 1,307
31 11 Magee 1,400
32 12 Giljum 1,490
33 13 Sedeghi 1,515
34 14 Nguyen 1,525
35 15 Dumas 1,450
41 16 Maduro 1,400
41 17 Smith 940
42 18 Nozaki 1,200
42 19 Patel 795
43 20 Newman 750
43 21 Markarian 850
44 22 Chang 800
34 23 Patel 795
45 24 Dancs 860
45 25 Schwartz 1,100
25 rows selected.
Next is code that displays the same 25 rows, grouped (partitioned) by DEPT_ID and sorted within DEPT_ID by highest to lowest SALARY, and a rank number (RNK) by SALARY. (If two employees have the same salary within the same department, then they have the same rank.):
Code:
select dept_id
,rank() over (partition by dept_id order by salary desc) RNK
,id
,last_name
,salary
from emp
/
DEPT_ID RNK ID LAST_NAME SALARY
-------- ---------- --- ------------ -------
10 1 4 Quick-To-See 1,450
31 1 3 Nagayama 1,400
31 1 11 Magee 1,400
32 1 12 Giljum 1,490
33 1 13 Sedeghi 1,515
34 1 14 Nguyen 1,525
34 2 23 Patel 795
35 1 15 Dumas 1,450
41 1 2 Ngao 1,450
41 2 16 Maduro 1,400
41 3 6 Urguhart 1,200
41 4 17 Smith 940
42 1 7 Menchu 1,250
42 2 18 Nozaki 1,200
42 3 19 Patel 795
43 1 8 Biri 1,100
43 2 21 Markarian 850
43 3 20 Newman 750
44 1 9 Catchpole 1,300
44 2 22 Chang 800
45 1 10 Havel 1,307
45 2 25 Schwartz 1,100
45 3 24 Dancs 860
50 1 1 Velasquez 2,500
50 2 5 Ropeburn 1,550
25 rows selected.
Notice that DEPT_ID 41 has four employees with four different salaries, therefore, there are four ranks: 1, 2, 3, and 4.
Now, to display just the top rankings for each group, you can place the code, above, into an "in-line" view, and use "RNK" in the outer WHERE clause to obtain just the top-ranked rows for each group:
Code:
select *
from (select dept_id
,rank() over (partition by dept_id order by salary desc) RNK
,id
,last_name
,salary
from emp)
where RNK = 1
/
DEPT_ID RNK ID LAST_NAME SALARY
-------- ---------- --- ------------ -------
10 1 4 Quick-To-See 1,450
31 1 3 Nagayama 1,400
31 1 11 Magee 1,400
32 1 12 Giljum 1,490
33 1 13 Sedeghi 1,515
34 1 14 Nguyen 1,525
35 1 15 Dumas 1,450
41 1 2 Ngao 1,450
42 1 7 Menchu 1,250
43 1 8 Biri 1,100
44 1 9 Catchpole 1,300
45 1 10 Havel 1,307
50 1 1 Velasquez 2,500
13 rows selected.
The above model should allow you to apply the technique to your PRINPORT_90 table to obtain similar results.
If you have trouble making the jump from the example to your PRINPORT_90 table, please post your questions here.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
