SQL Rank based on column value

Question

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

Answer 1

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.

Answer 2

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