SQLize | PHPize | SQLtest

CREATE EXTENSION hstore; DROP TABLE IF EXISTS tmp_h_sometable; CREATE TABLE tmp_h_sometable ( h hstore NULL, dt_created timestamp NULL DEFAULT 'now'::text::timestamp without time zone ); INSERT INTO tmp_h_sometable (h,dt_created) VALUES ('"_ID"=>"1", "op_ts"=>"2021-03-08 08:56:46.388", "table"=>"sometable", "MAJDAT"=>"20210305", "MAJHMS"=>"115646", "op_type"=>"U"','2021-03-15 14:37:09.033001'), ('"_ID"=>"2", "op_ts"=>"2021-03-08 09:06:37.280", "table"=>"sometable", "MAJDAT"=>"20210308", "MAJHMS"=>"120637", "op_type"=>"D"','2021-03-15 14:51:34.173244'), ('"_ID"=>"3", "op_ts"=>"2021-03-08 09:12:35.368", "table"=>"sometable", "MAJDAT"=>"20210107", "MAJHMS"=>"121235", "op_type"=>"U"','2021-03-15 15:13:11.715818'), ('"_ID"=>"2", "op_ts"=>"2021-03-08 09:08:08.358", "table"=>"sometable", "MAJDAT"=>"20210309", "MAJHMS"=>"120808", "op_type"=>"U"','2021-03-15 15:18:45.857119'), ('"_ID"=>"2", "op_ts"=>"2021-03-08 09:06:37.282", "table"=>"sometable", "MAJDAT"=>"20210303", "MAJHMS"=>"120637", "op_type"=>"I"','2021-03-15 15:29:43.868558'), ('"_ID"=>"3", "op_ts"=>"2021-03-08 09:13:48.156", "table"=>"sometable", "MAJDAT"=>"20210305", "MAJHMS"=>"21348", "op_type"=>"D"','2021-03-15 15:34:11.050351'), ('"_ID"=>"1", "op_ts"=>"2021-03-08 08:55:38.698", "table"=>"sometable", "MAJDAT"=>"20210208", "MAJHMS"=>"115538", "op_type"=>"I"','2021-03-15 14:35:37.133757'), ('"_ID"=>"1", "op_ts"=>"2021-03-08 08:56:46.395", "table"=>"sometable", "MAJDAT"=>"20210307", "MAJHMS"=>"15646", "op_type"=>"U"','2021-03-15 14:56:21.570984'), ('"_ID"=>"4", "op_ts"=>"2021-03-08 09:07:59.823", "table"=>"sometable", "MAJDAT"=>"20210208", "MAJHMS"=>"20759", "op_type"=>"I"','2021-03-15 14:56:21.570984'), ('"_ID"=>"4", "op_ts"=>"2021-03-08 09:05:40.488", "table"=>"sometable", "MAJDAT"=>"20210301", "MAJHMS"=>"120540", "op_type"=>"I"','2021-03-15 15:29:33.790932'); SELECT _id, table_name, ts, op_ts, op_type, dt_created, row_number()over(partition by _id, table_name order by op_ts desc) as rn FROM (SELECT (tmp_h_sometable.h -> '_ID') as _id, (tmp_h_sometable.h -> 'table') as table_name, (tmp_h_sometable.h -> 'op_ts') as op_ts, to_timestamp((tmp_h_sometable.h -> 'MAJDAT') || lpad((tmp_h_sometable.h -> 'MAJHMS'),6,'0'),'yyyymmddhh24miss')::timestamptz as ts, (tmp_h_sometable.h -> 'op_type') as op_type, dt_created FROM tmp_h_sometable) tmp1; SELECT _id, table_name, ts, dt_created FROM (SELECT _id, table_name, ts, op_type, dt_created, row_number()over(partition by _id, table_name order by op_ts desc) as rn FROM (SELECT (tmp_h_sometable.h -> '_ID') as _id, (tmp_h_sometable.h -> 'table') as table_name, (tmp_h_sometable.h -> 'op_ts') as op_ts, to_timestamp((tmp_h_sometable.h -> 'MAJDAT') || lpad((tmp_h_sometable.h -> 'MAJHMS'),6,'0'),'yyyymmddhh24miss')::timestamptz as ts, (tmp_h_sometable.h -> 'op_type') as op_type, dt_created FROM tmp_h_sometable) tmp1) tmp2 WHERE rn = 1 and op_type<>'D';