I created table.

CREATE TABLE test_tab(
ID INT,
FIRSTNAME VARCHAR(40),
TS TIMESTAMP)

And insert values into it.

INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (1, 'Jhon', '2018-06-05 00:11:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (2, 'Jhon', '2018-06-15 00:14:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (3, 'Jhon', '2018-06-19 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (4, 'Mike', '2018-06-05 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (5, 'Mike', '2018-06-15 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (6, 'Mike', '2018-06-20 00:10:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (7, 'Lis', '2018-06-05 00:13:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (8, 'Lis', '2018-06-15 00:17:56');
INSERT INTO test_tab (ID, FIRSTNAME, TS) VALUES (9, 'Lis', '2018-06-21 00:10:56');

I need to delete rows so that only one row exist for one first name, leave row with maximum TS. It is the example of my request. How can I delete it?

SELECT DISTINCT firstname
FROM test_tab
GROUP BY firstname
HAVING COUNT(firstname) > 1
union 
select firstname from test_tab where ts = (select max(ts) from test_tab)

CodePudding user response：

Try this.

DELETE FROM TEST_TAB T
WHERE NOT EXISTS
(
  SELECT 1
  FROM TEST_TAB G
  WHERE G.FIRSTNAME = T.FIRSTNAME
  HAVING MAX (G.TS) = T.TS
);

SELECT * FROM TEST_TAB;

fiddle

CodePudding user response：

You can delete from a derived table as long as there is a bijection to the underlying table:

delete from (
    select t.*, row_number() over (partition by FIRSTNAME order by ts) as rn
    from test_tab t
)
where rn > 1;

Fiddle