How do I return single orderid having same rank?

Question

I have the following output:

Orderid	Time	State	Order_rank
1	10.15	mfr	1
1	10.15	delivered	1
2	12.10	picked	1
2	12.10	mfr	1

Here I have ranked the order ids wrt to the latest time and put the condition in where clause "where order_rank = 1", but I want to return a single entry for one orderjobid, i.e. for orderid 1 I want to return the delivered state entry and for orderid 2 I want mfr state entry etc.

This is just a sample data, I have more than 1000s of orderids, how do I return single entry for the same rank?

Answer 1

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