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.