Selecting Max of two columns (revisited)

Question

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).

Answer 1

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?

Answer 2

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.