Taz Taz - 7 months ago 21
SQL Question

how to user over partition by in case statement

The below are the results from a select statement

u_id | l_id | risk | Count | Avg
----------------------------------------
| 1 | 10 |Critical | 3 | 33.33
| 1 | 10 |Limited | 3 | 33.33
| 1 | 10 |Medium | 2 | 22.22
| 1 | 10 |Significant| 1 | 11.11
| 2 | 9 |Medium | 1 | 50.00
| 2 | 9 |Significant| 1 | 50.00
| 3 | 8 |Limited | 1 | 50.00
| 3 | 8 |Medium | 1 | 50.00


I used a case statement to get results from the select statement but I want to get results making u_id and l_id as unique.

case statement:

SELECT u_id, l_id, (CASE WHEN risk = 'Critical' THEN 'Critical'
WHEN risk = 'Significant' AND avg >= 50 THEN 'Critical'
WHEN risk = 'Significant' AND risk <> 'Critical' THEN 'Significant'
WHEN risk = 'Medium' AND avg >= 50 THEN 'Medium'
ELSE 'Limited' -- OVER (PARTITION BY u_id,l_id)
END) as crr
From
( select ...(select ... ) a .. ) b


actual results

u_id | l_id | risk
--------------------------
| 1 | 10 |Critical
| 1 | 10 |Limited
| 1 | 10 |Limited
| 1 | 10 |Significant
| 2 | 9 |Medium
| 2 | 9 |Critical
| 3 | 8 |Limited
| 3 | 8 |Medium


what I am expecting is

u_id | l_id | risk
--------------------------
| 1 | 10 |Critical
| 2 | 9 |Critical
| 3 | 8 |Medium


Thanks.

Answer

Postgres has a nice syntax for this: distinct on:

SELECT DISTINCT ON (u_id, l_id) u_id, l_id,
       (CASE WHEN risk = 'Critical' THEN 'Critical'
             WHEN risk = 'Significant' AND avg >= 50 THEN 'Critical'
             WHEN risk = 'Significant' AND risk <> 'Critical' THEN 'Significant'
             WHEN risk = 'Medium' AND avg >= 50 THEN 'Medium'
             ELSE 'Limited' -- OVER (PARTITION BY u_id,l_id) 
        END) as crr 
FROM ( select ...(select ... ) a .. ) b 
ORDER BY u_id, l_id, ??;

The key here is the ORDER BY. I'm guessing you want the highest risk. One method is:

ORDER BY u_id, l_id,
          (case when risk = 'Critical' then 1
           . . .
          )
Comments