psql max group by just for some columns

Question

I have a problem in a psql query. I don't know how to select only the maximum value from a subset of two columns. It's very hard to explain the problem without an example, so I write one:

I have a table like that:

   Athlete | Category | Points

      at1  |  cat1    | 100
      at1  |  cat1    | 90
      at1  |  cat1    | 80
      at1  |  cat2    | 95
      at2  |  cat1    | 97
      at2  |  cat2    | 60
      at2  |  cat2    | 71

I would like to keep for every Athlete the maximum points in every category. So the final table should be like that:

   Athlete | Category | Points

      at1  |  cat1    | 100
      at1  |  cat2    | 95
      at2  |  cat1    | 97
      at2  |  cat2    | 71

Answer 1

This is a classic usecase for the group by clause to return only distinct combinations of athlete and category. Then, max(points) could be applied to each combination:

SELECT   athlete, category, MAX(points)
FROM     mytable
GROUP BY athlete, category