'How do I return single orderid having same rank?

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?

sqlsql-order-bysnowflake-cloud-data-platform


Solution 1:[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

Solution 2:[2]

you could try row_number() over (partition by Orderid order by State) and select the first one

Solution 3:[3]

Extend your ranking to include not only the time, but also an expression of state, like.

Order time desc, 
     case state when 'picked' then 1
                When 'mfr' then 2
                When 'delivered' then 3
                ...
     End asc

This way we assign a sort order 1,2,3 to each state.

Edited to make it correct as per comments from @simeon-pilgrim (thanks)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Simeon Pilgrim
Solution 2 flmelody
Solution 3

Related Questions