Problem with MAX() and `group by` in an SQL query
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 idtid
= team idpoints
= 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 thetid
that has MAX(points) for every pid? I triedSELECT pid, tid, MAX(points) from teampoints group by pid;
but understandably, that would not workI'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: tid | uid
events: eid
problems: pid | eid
submissio开发者_如何学Cns: subid | pid | uid | eid | points | subts
Some notes: - problems belong to events - users belong to teams - submissions belong to problems(pid) and users(uid). the submissions table has a redundant eid field, which can always be determined from the pid.
The use case is:
- users form teams. users are identified by
uid
, teams bytid
. Team members are stored in teammembers table. - users can make submissions, which are stored in submissions table. submissions are awarded points. subts is the unix timestamp of when the submission was made.
- users can submit multiple times for the same problem. the latest submission (max subts) is counted.
now, in this set up I want to find the teamname
that has scored maximum points
for any given event (eid).
I hope this makes my situation clear. I wanted to ask only what I needed to know. I furnish these details up an a request in the comments.
EDIT: the query that generated the teampoints
table is:
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
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.
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 ....
You could store 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
Note that you will get doubles when two teams have equal points on a project.
If you would post your query, it would be easier for us trying to optimize it io trying to invent it all over.
精彩评论