Postgres 14 delete with count in where clause

Question

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...

With everyone's idea and combine with this

SELECT dept, SUM(expense) FROM records 
WHERE ROW(year, dept) IN (SELECT x, y FROM otherTable)
GROUP BY dept;

link

I was able to make the query I want

Demo

The quoestion is a bit confusing, it would help, if you told, what is the desired result with the additional data like `insert into code select 7, 'name3', 1;` — n3ko, Nov 10 '22 at 12:41
If you want to have two copies of the table being referenced at the same time, you need to assign them different aliases and refer to them by those aliases. Also, you need to use HAVING, not WHERE, to filter on aggregated values. — jjanes, Nov 10 '22 at 16:53
@MarkRotteveel i actually havent tried it ..i thought that i need to use `where clause` on this. thank you for the idea — guradio, Nov 11 '22 at 01:26

score 1 · Answer 1 · answered Nov 10 '22 at 12:18

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);

score 1 · Answer 2 · answered Nov 10 '22 at 12:20

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;

Postgres 14 delete with count in where clause

2 Answers2