0

I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt: enter image description here

In my final output, I would like only to keep rows with the highest rank (in green). And I was using the following query, which works to get the output, but I have to transform it into Redshift because the visualization tool is only using Redshift and Redshift doesn't recognize DISTINCT ON.

select distinct on customer_id
id_created,
customer_id,
call_start, 
status_prior,   
status_after,   
rank() over (partition by customer_id order by call_start) as rank
order by customer_id, rank desc;  
0

This is my workaround, that works with Redshift:

select * from (select id_created, customer_id, call_start, status_prior, status_after,
rank() over (partition by customer_id order by call_start desc) as rank ) t where rank=1;

It seems to work, however, the processing time is longer, because of the subquery.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.