Here is a task I need to get three elements based on the given conditions: three elements: user_id, order_time, ordered_subject

1. each unique user_id
2. earliest order_time
3. ordered_subjects' order should be app-> acc ->ayy
4. if there are several order_time are the same, you should take only one subject followed by the 3rd requirement

original table: user_order

expected table:

I come up with the idea of case when and row_number() over, but it doesn't work

the code I tried:

select 
a.uid, 
a.subject, 
b.min_time,
   (case when "app" then 1 
           when "acc" then 2 
           when "ayy" then 3 
           else 4 end) as rn, 
   row_number() over(partition by 
                       concat(uid,order_id) 
                       order by 
                       rn)
from (
   select uid, min(order_time) as min_time
   from user_order
   group by
   uid
   ) as b
-- join
user_order as a
-- on
where
a.uid = b.uid
and
b.min_time = a.order_time 

How should I fix this? Thank you:)

CodePudding user response：

You want one result row per user. Per user you want the earliest order and if there is more than one order on the earliest date you prefer the order subject app over acc and acc over ayy.

You want to use ROW_NUMBER, so partition by user ID and order by date and the order subject in the desired order.

select user_id, order_time, ordered_subject
from
(
  select
    user_id, order_time, ordered_subject,
    row_number() over
      (partition by user_id
       order by order_time,
                case ordered_subject
                  when 'app' then 1
                  when 'acc' then 2
                  when 'ayy' then 3
                  else 4
                end) as rn
  from mytable
) numbered
where rn = 1
order by user_id;