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;  

1 Answer 1

1

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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