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

Question

I am writing a particularly troublesome request. It comes down to the following:

I get the structure table:

pid | tid | points 

after a pretty big request.

For ease of explanation:

• pid = id problem
• tid = team identifier
• points = points awarded to this team for this problem.

I want to find the team that scored the maximum points for a particular pid.

My question is twofold:

• If it was a simple table that went by the name teampoints , how do I get a tid that has MAX (dots) for each pid? I tried SELECT pid, tid, MAX(points) from teampoints group by pid; but it’s clear that it didn’t work

• I came to this result after a rather large query. If the answer to my first involves selecting data from teampoints again, is there a way to do this without having to compute the entire table again?

thanks

PS: I am using mysql.

---

GORY DETAILS: WIRELESS SECURITY

I have several tables in my system, their respective structures:

 users: uid teams: tid | eid | teamname teammembers: tid | uid events: eid problems: pid | eid submissions: subid | pid | uid | eid | points | subts 

Some notes: - problems relate to events - users belong to teams - representations relate to problems (pid) and users (uid). there is a redundant eid field in the view table, which can always be determined from pid.

Use case:

• users form teams. users are identified by uid , tid commands. Team members are stored in the teammembers table.
• users can create materials that are stored in the presentation table. applications for points. subts is the unix timestamp when the transfer was made.
• users can post multiple times on the same issue. last feed (maximum values) is calculated.

Now, in this setting, I want to find the teamname that scored the maximum points for any given event (eid).

Hope this changes my situation. I wanted to ask only what I needed to know. I provide this data in the comments.

EDIT: The query that generated the teampoints table:

 SELECT s.pid, teamlatest.tid, s.points FROM submissions s, teammembers tm, teams t, (SELECT max(maxts) AS maxts, pid, tid FROM (SELECT latest.maxts, latest.pid, t.tid FROM submissions s, teams t, teammembers tm, (SELECT max(subts) AS maxts, pid, uid FROM submissions WHERE eid=3 AND status='P' GROUP BY pid, uid ) AS latest WHERE s.uid=latest.uid AND s.pid=latest.pid AND s.subts=latest.maxts AND latest.uid=tm.uid AND tm.tid=t.tid AND t.eid=3 ) AS latestbyteam GROUP BY pid, tid) AS teamlatest WHERE s.pid=teamlatest.pid AND teamlatest.tid=t.tid AND t.tid=tm.tid AND tm.uid=s.uid AND s.subts=teamlatest.maxts 

Answer 1

• One of the methods:

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

  Other using connections:

   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 temporary table for a 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 upper counters.

Answer 2

 select pid, tid, points from teampoints tp where not exists ( select 1 from teampoints tp1 where tp.pid = tp1.pid and tp.points < tp1.points) 

or somesuch ....

Answer 3

You can save the results of your first query in a temporary table and try something like this

 SELECT pid, tid, points FROM teampoints tp INNER JOIN ( SELECT pid, points = MAX(points) FROM teampoints GROUP BY pid ) tp_max ON tp_max.pid = tp.pid AND tp_max.points = tp.points 

Please note that you will get a doubling when two teams have the same points in the project.

If you post your request, it will be easier for us to try to optimize it, trying to come up with all this.