CODEWITHSUNDEEP
sqlpostgresql
Nemo
Nemo

Reputation: 1111

custom postgresql

I have a scenario where I'm trying to blend two data sets to get the output. The first table or table1 has assignment ID which was routed to two people. It also shows the score_id for these people. Their scores and date on which they were assigned task is also given.

enter image description hereTable2 shows more details of dispatcher scheduling. For this example, you will see 20 records. For each scheduling event, for each res_score_id in table 1, dispatcher looks for top 10 resources and their score from table2. So, for res_score_id = 2, you will see first 10 records in table 2 with final_indx = 2. Likewise for res_score_id = 1, there are 10 people with final_indx = 1 in table2.

What I want to get is the dispatcher_id of the first person in each top 10 cluster, and his score as highlighted in excel row10 in table2, row18 in table2.

Then I want to calculate the difference in score between the top first dispatcher and the person1(from table 1) and likewise get the score of top dispatcher in second cluster in table2 and subtract it from person2(table1)'s score.

Upvotes: 0

Views: 24

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You want to rank your table2 records. You do this with RANK (or DENSE_RANK) if you want to consider ties, or with ROW_NUMBER if you don't. Rank 1 is the best row per final_indx, i.e. the one with the highest score and you only keep these. Then join table1.

select
  t1.*,
  t2.dispatcher_id,
  t2.dispatcher_score,
  t2.dispatcher_score - t1.score as delta_score
from
(
  select 
    dispatcher_id,
    dispatcher_score,
    final_indx,
    rank() over (partition by final_indx order by dispatcher_score desc) as rnk
  from table2
) t2
join table1 t1 on t1.res_score_id = t2.final_indx
where t2.rnk = 1;

Upvotes: 1

Related Questions