Postgres 14 delete with count in where clause-CodePudding

I wanted to delete all records except the one with the highest value so I did

CREATE TABLE code (
  id SERIAL, 
  name VARCHAR(255) NOT NULL ,
  value int NOT NULL
);

INSERT INTO code (name,value) VALUES ('name',1);
INSERT INTO code (name,value) VALUES ('name',2);
INSERT INTO code (name,value) VALUES ('name',3);
INSERT INTO code (name,value) VALUES ('name1',3);
INSERT INTO code (name,value) VALUES ('name2',1);
INSERT INTO code (name,value) VALUES ('name2',3);

Example I want to delete all records except the one with the highest value on value column

I am expecting to get result as:

name 3
name1 3
name2 3

I tried doing

DELETE FROM code where value != (select MAX(value) value from code where count(code) > 1)

But I'm getting an error like:

ERROR: aggregate functions are not allowed in WHERE
LINE 1: ...value != (select MAX(value) value from code where count(code...

Demo

CodePudding user response：

Your query makes no sense. Try this:

DELETE FROM code
where value <> (select value
                FROM (SELECT count(*) AS count,
                             value
                      from code
                      GROUP BY value) AS q
                ORDER BY count DESC
                FETCH FIRST 1 ROWS ONLY);

CodePudding user response：

The fast and easy solution would be:

BEGIN;
SELECT name,max(value) INTO temp t FROM code group by 1;
TRUNCATE code;
insert into code SELECT * FROM t;
END;

Or you can do like:

BEGIN;
DELETE FROM code USING (SELECT name,max(value) FROM code group by 1) a WHERE code.name=a.name AND code.value!=a.max;
END;