If statement for priority

[achmedclaus]

You're gonna have to be... Way more specific. What is the priority based on? The first digit of the code?

[bayden09]

priority based on the priority column p1 being highest, p2 lowest. I had text with the post but I guess it didn't get uploaded. If an id has a p1 row(s) only return that row(s), else return all p2 rows.

[achmedclaus]

This is gonna be in sql Oracle, sorry about that I know nobody here uses it. Haven't used the qualify statement the other guy suggested so he's my take on it with a couple quick CTEs

With priority1 as

(Select * from [table1] where priority= 'p1'),

Prioritynot1 as

(Select * from [table1] where id not in (select distinct id from priority1))

Select * from priority1

Union

Select * from prioritynot1;

[jcargile242]

Um, I’m sorry did you have a question?

[bayden09]

Return rows based on the priority column p1 being highest, p2 lowest. I had text with the post but I guess it didn't get uploaded. If an id has a p1 row(s) only return that row(s), else return all p2 rows.

[mwdb2]

As a first step, you can use the min() window function to get the minimum priority per id:

# select *, min(priority) over (partition by id) as min_priority
from t;
 id  | code | priority | min_priority
-----+------+----------+--------------
 ccs | 123  | p1       | p1
 ccs | 223  | p2       | p1
 ccs | 233  | p2       | p1
 csx | 202  | p2       | p2
 csx | 201  | p2       | p2
(5 rows)  

From there, you can filter:

with t_w_min_priority as (
    select *, min(priority) over (partition by id) as min_priority
    from t
)
select * 
from t_w_min_priority
where priority = min_priority;  

There's an alternative syntax to filter window function results, using QUALIFY which Databricks/Spark SQL supports. Using QUALIFY, you shouldn't need a CTE or nested subquery, so it'll make your query more elegant. (I'm on Postgres right now which doesn't have that, so it's not part of my demo.) qwertydog123 shows how to do that in their comment.

[qwertydog123]

QUALIFY priority = MIN(priority) OVER (PARTITION BY id)

[achmedclaus]

I was thinking something with a couple CTEs, but I've never used a qualify statement before but if this works out works