Selecting Max of two columns (revisited)

I asked a similar question before only to later discover that what I thought was the answer didn't work because I hadn't asked the right question (and hadn't noticed that the answerer told me this). To revisit, I have a table of comparisons. I'm trying to select the row with the maximum version for each set of two students. So what I had been doing:

SELECT subID1, subID2, stu1,stu2,comparisonID,MAX(stu1vers+stu2vers) AS maxvers 
FROM comparisons
WHERE assignmentID=9 AND stu1!=stu2 
GROUP BY stu1,stu2;
+--------开发者_运维知识库+--------+------+------+--------------+---------+
| subID1 | subID2 | stu1 | stu2 | comparisonID | maxvers |
+--------+--------+------+------+--------------+---------+
|     15 |     11 |    1 |    6 |           64 |       6 |
|     11 |      3 |    6 |    1 |           55 |       5 |
+--------+--------+------+------+--------------+---------+

wouldn't work, because there I only needed the row where maxvers was 6. The answer I got last time was:

SELECT subID1,subID2,stu1,stu2, comparisonID 
FROM comparisons WHERE stu1Vers + stu2Vers = (
    SELECT MAX(stu1Vers+stu2Vers) 
    FROM comparisons 
    WHERE stu1 != stu2 AND assignmentid=9
) AND stu1!=stu2 AND assignmentid=9 
GROUP BY stu1,stu2;

Which I really thought worked - only to discover that this queries for the maximum version in the table for that assignment, then looks for rows that match that maximum version. But this is no good, because two students might have a lower version number:

+--------+--------+------+------+--------------+---------+
| subID1 | subID2 | stu1 | stu2 | comparisonID | maxvers |
+--------+--------+------+------+--------------+---------+
|     44 |     23 |   37 |   36 |          153 |       2 |
|     44 |     36 |   37 |   39 |          156 |       3 |
|     44 |     34 |   37 |   40 |          154 |       3 |
|     36 |     23 |   39 |   36 |           95 |       3 |
|     36 |     34 |   39 |   40 |           96 |       4 |
...
+--------+--------+------+------+--------------+---------+

There I need to select all of those records, as each combination of stu1 and stu2 is unique. How do I the rows where max(sub1vers+sub2vers) for each combination of stu1,stu2 (that is, as in the first table up there, where I still need just comparisonID 64).

Edit The MySQL-specific query would yield incorrect values for the non-aggregate columns. Please use the portable query instead.

Assuming that you are running under MySQL (based on your question's tag as well as your original SQL query), then you are able to issue the following statement:

SELECT subID1, subID2,stu1,stu2,comparisonID,MAX(stu1vers+stu2vers) AS maxvers
 FROM comparisons
 WHERE assignmentID=9 AND stu1!=stu2
 GROUP BY LEAST(stu1,stu2), GREATEST(stu1,stu2);

If you require better portability (also being able to issue the query on e.g. Postgres), you need a slightly more complex query, here using a single JOIN:

SELECT c1.subID1, c1.subID2,c1.stu1,c1.stu2,c1.comparisonID,c2.versmax
 FROM comparisons AS c1
 INNER JOIN (
  SELECT
    LEAST(stu1,stu2) AS stuA,
    GREATEST(stu1,stu2) AS stuB,
    MAX(stu1vers+stu2vers) AS versmax
   FROM comparisons
   WHERE assignmentID=9 AND stu1<>stu2
   GROUP BY stuA, stuB
 ) AS c2
 ON ((c1.stu1=c2.stuA AND c1.stu2=c2.stuB) OR
     (c1.stu2=c2.stuA AND c1.stu1=c2.stuB)
    ) AND c1.stu1vers+c1.stu2vers=c2.versmax
 WHERE c1.assignmentID=9 AND c1.stu1<>c1.stu2;

Note that the more portable query may still return two rows for a unique pair of students if both combinations yield the same maxvers (unless you decide to provided a rule to discriminate between the two), e.g.:

+--------+--------+------+------+--------------+---------+ 
| subID1 | subID2 | stu1 | stu2 | comparisonID | maxvers | 
+--------+--------+------+------+--------------+---------+ 
|     15 |     11 |    1 |    6 |           64 |       6 | 
|     11 |      3 |    6 |    1 |           55 |       6 | 
+--------+--------+------+------+--------------+---------+ 

SELECT subID1, subID2, stu1,stu2,comparisonID,MAX(stu1vers+stu2vers) AS maxvers
FROM comparisons
WHERE assignmentID=9 AND stu1!=stu2
GROUP BY stu1,stu2
ORDER BY MAX(stu1vers+stu2vers) DESC
LIMIT 1

Or am I thinking wrong?

Sorry that I didn't understand your question properly last time you asked. How about this:

SELECT
    subID1,
    subID2,
    T3.stu1,
    T3.stu2,
    comparisonID,
    stu1vers + stu2vers AS maxvers
FROM (
    SELECT assignmentId, stu1, stu2, MAX(vers) AS maxvers
    FROM (
        SELECT
            assignmentId,
            stu1vers + stu2vers AS vers,
            LEAST(stu1, stu2) AS stu1,
            GREATEST(stu1, stu2) AS stu2
        FROM comparisons
        WHERE stu1 <> stu2) AS T1
    GROUP BY assignmentId, stu1, stu2
) AS T2
JOIN comparisons AS T3
    ON T2.stu1 = LEAST(T3.stu1, T3.stu2)
    AND T2.stu2 = GREATEST(T3.stu1, T3.stu2)
    AND T2.maxvers = T3.stu1vers + T3.stu2vers
    AND T2.assignmentId = T3.assignmentId
WHERE T3.assignmentId = 9

This groups by stu1 and stu2 to find the maximum versions, the self-joins with the comparison table to fetch the remaining columns for the corresponding row. The order of stu1 and stu2 is assumed to be irrelevant.