Search code examples
sqlgreatest-n-per-groupgreenplum

Group by Issue GreenPlum


I want to do a group by in a table with ~300 columns. There are orders and their status is updated for the next 30 days. I want to pick the order with the max(update_time). So my query is something like this:

select order_num,status,order_date,max(update_date)     from orders
where order_date = '2021-07-01'
     and update_date between '2021-07-01' and '2021-08-01'
 group by 'primary_key';

Is there a way to write the query without adding an aggregate function on all 300 columns?


Solution

  • you are looking for this :

    select * from (
       select * , row_number() over (partition by order_num order by update_date desc) rn
       from orders
    ) t
    where rn = 1;