Redshift: Multiple rows for same ID in table, discard older rows?

Use a subquery and WHERE:

CREATE TEMP TABLE foo as 
    SELECT t.*
    FROM (SELECT t.*,
                 ROW_NUMBER OVER (PARTITION BY id ORDER BY update_time DESC) AS row_number
          FROM my_table t
         ) t
    WHERE row_number = 1;

EDIT:

For a DELETE, you can use using:

delete from my_table
    using (select t.id, max(t.update_time) as max_ut
          from my_table t
          group by t.id
         ) tt
    where my_table.id = tt.id and
          my_table.update_time < tt.max_ut;

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top