CODEWITHSUNDEEP
sqlamazon-redshiftquery-performance
diptiranjan pradhan
diptiranjan pradhan

Reputation: 81

Redshift last_value to retrieve the most current row

Below is one simple way to get the most recent version of a row. While this works ‘okay’ for a single row, it does slow down when each row needs to be evaluated. 

SELECT * 
FROM dev.A t1 
where to_char(last_operation_date,'yyyymmdd') = (select max(to_char(last_operation_date,'yyyymmdd')) 
                                                 from dev.A t2 
                                                 where t1.inte_id = t2.inte_id)
and t1.inte_id = 'PPP2434'

Upvotes: 0

Views: 3185

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

You can use the ROW_NUMBER() function to find the latest row per inte_id without using a correlated subquery. Correlated subqueries often require a nested loops step which slows them down.

SELECT * 
FROM (
    SELECT *
          ,ROW_NUMBER() OVER( PARTITION BY inte_id ORDER BY last_operation_date DESC ) last_op_order
    FROM dev.A t1 
    and t1.inte_id = 'PPP2434'
    )
WHERE last_op_order = 1;

Upvotes: 3

Related Questions