Problem with MAX() and `group by` in an SQL query

Question

I'm writing a particularly troublesome query. It boils down to this: I obtain a table of the structure: ``` pid | tid | points ``` after a pretty large query. For ease of explanation: - `pid` = problem id - `tid` = team id - `points` = points awarded to that team for that problem. I want to find the team who has scored max points for a particular pid. My question is twofold: If this were a simple table that went by the name `teampoints`, how do I get the `tid` that has MAX(points) for every pid? I tried `SELECT pid, tid, MAX(points) from teampoints group by pid;` but understandably, that would not work I've arrived at this result after a rather large query. If the answer to my first involves selecting data from `teampoints` again, is there any way to do that without having to calculate the whole table again? Thanks PS: I use mysql. GORY DETAILS: TREAD WITH CAUTION I have a few tables in my system, their relevant structures being: ``` users: uid teams: tid | eid | teamname teammembers:…

Accepted Answer

One way:

```
SELECT pid, tid, points 
  FROM teampoints
  WHERE (pid, points) IN (
      SELECT pid, MAX(points) 
        FROM teampoints GROUP BY pid
  )
```

Another, using joins:

```
SELECT s1.*
    FROM teampoints AS s1
    LEFT JOIN teampoints AS s2
       ON s1.pid = s2.pid
       AND s1.points < s2.points
    WHERE s2.tid IS NULL
```

You can `INSERT INTO` a temporary table for the complex query:

```
CREATE TEMPORARY TABLE scores (
    pid INT, tid INT, points INT,
    KEY pp (pid, points)
);
INSERT INTO scores (pid, tid, points) 
    SELECT <a complex query>
```

then SELECT the top scorers from that.

Problem with MAX() and `group by` in an SQL query

Solution

Problem

Related problems