Assuming you code looks something like this:
SELECT * FROM (
SELECT
order_id,
time,
state,
RANK() over (partition by order_id order by time) as order_rank
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
)
WHERE order_rank = 1;
which gives the output you have.
Not the solution you are looking for, but you can move from the RANK and sub-select and WHERE to use QUALIFY and hide the order_rank line:
SELECT
order_id,
time,
state
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
QUALIFY RANK() over (partition by order_id order by time) = 1;
But as userMT notes, you want to put a numeric ranking into the selection
like:
case state
when 'delivered' then 1
when 'mfr' then 2
when 'picked' then 3
else 4
end as state_rank,
so if you have it as a separate field, it's easier to read:
SELECT
order_id,
time,
state,
case state
when 'delivered' then 1
when 'mfr' then 2
when 'picked' then 3
else 4
end as state_rank
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
QUALIFY RANK() over (partition by order_id order by time,state_rank) = 1;
giving:
| ORDER_ID |
TIME |
STATE |
STATE_RANK |
| 1 |
10.15 |
delivered |
1 |
| 2 |
12.10 |
mfr |
2 |
but you can mash the case into the rank also:
SELECT
order_id,
time,
state
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
QUALIFY RANK() over (partition by order_id order by time, case state when 'delivered' then 1 when 'mfr' then 2 when 'picked' then 3 else 4 end) = 1;
gives:
| ORDER_ID |
TIME |
STATE |
| 1 |
10.15 |
delivered |
| 2 |
12.10 |
mfr |