How to delete the highest value in a group

Question

I'm trying to delete the highest value from a group. Given this data:

group_table

group_id | st_area
---------------
1        |  20
1        |  30
2        |   1
2        |   1
2        | 200

I'd like to remove the second and fifth rows. I know how to select the highest from each group:

SELECT max(area) FROM group_table GROUP BY group_id

I can't figure out how to formulate a delete statement that will accept that as a subquery, though. When I try delete from group_table where st_area = max(st_area);

I get an error: aggregates not allowed in WHERE clause.

One thing I have learned pretty quickly on SO is that I'm not a terribly good communicator. If what I'm asking is unclear & you're feeling patient, I'll certainly try to clarify.

Thanks!

Answer 1

Do you have some primary key in your table? According to your specification, you don't.

In this case:

DELETE 
  FROM group_table 
  WHERE (group_id, st_area) 
    IN (SELECT group_id, MAX(st_area) FROM group_table GROUP BY group_id);

But if you have TWO maximum-value rows, it will delete both.

If in this case you want to delete just one, you should add an id column for the selection:

DELETE 
  FROM group_table
  WHERE (id) 
    IN (
      SELECT MAX(id) FROM group_table 
        WHERE (group_id, st_area) 
          IN ( SELECT group_id, MAX(st_area) 
                 FROM group_table 
                 GROUP BY group_id) 
        GROUP BY group_id;

);

So you are selecting the maximum id for every maximum st_area for every group id.

In case you have a structure like this:

id | gid | area
 1    1     2
 2    1     1
 3    1     2

the first query will delete rows 1 and 3, and the second query just 3.