-1

I have a requirement to give rank based on the column value in table

Condition:

  • prioritize JobType in the order of 1: 75, 2: 76, 3: 80 , 4: 64
  • When there are two contacts with same JobType, prioritize RANK with OrderCount Desc
  • Table also has other JobTypes that are not listed
  • If there are other JobTypes, they should follow after matching the
    above condition
  • If the customer doesn't have any matching JobTypes: 75, 76, 80 , 64 The rank should be on the basis of OrderCount
  • JobType is associated with ContactPerson

Case 1: case 1 Case 2: case 2

kishore
  • 1
  • 2
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Aug 18 '22 at 02:50

2 Answers2

0

You are posting pictures instead of code, but anyway, using row_number(), rank(), or dense_rank() along with a CASE expression should do it.

Not really following all your logic but something like:

select a.*
    , row_number() over (
        order by case
        when job_type = '1:75' then 1,
        when job_type = '2:76' then 2,
        when job_type = '3:80' then 3,
        when job_type = '4:64' then 4,
        else 5 end case,
        orderCount desc
    ) as rnk
from mytable
Dale K
  • 25,246
  • 15
  • 42
  • 71
Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79
0
select a.*, row_number() over 
(partition by Market,Customer order by case
 when JobType = '75' then 1
 when JobType = '76' then 2
 when JobType = '80' then 3
 when JobType = '64' then 4
else 5 end , orderCount desc) as rnk
from mytable a

above query worked for me and I am testing it.

kishore
  • 1
  • 2