Two Column Max Selection (Revised)

Question

I asked a similar question until only to later discover that what I thought was the answer did not work, because I did not ask the right question (and hadn I noticed that the defendant told me this). To return, I have a comparison table. I am trying to select the row with the maximum version for each set of two students. So what I did:

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 | +--------+--------+------+------+--------------+---------+ 

does not work, because there I only need a line where maxvers is 6. The answer I received last time:

 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; 

What I really thought worked was only to find out that these are queries for the maximum version in the table for this purpose, and then it searches for rows matching this maximum version. But this is not good, because two students may 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 | ... +--------+--------+------+------+--------------+---------+ 

I need to select all of these entries, since each combination of stu1 and stu2 is unique. Like rows where max (sub1vers + sub2vers) for each combination stu1, stu2 (that is, as in the first table, where I still need only comparison code 64).

Answer 1

Change A MySQL-specific query will result in invalid values ​​for non-aggregate columns. Use a portable query instead.

Assuming you are working in MySQL (based on your question tag, as well as the original SQL query), you can make the following expression:

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

Strike>

If you need improved portability (also the ability to issue a request, for example, Postgres), you need a slightly more complex request using one JOIN here:

 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 a more portable query may still return two rows for a unique pair of students if both combinations give the same maxvers (unless you decide to provide a rule to distinguish between them), for example:

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

Answer 2

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 I'm wrong?

Answer 3

Sorry I didn’t get your question right the 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 

These groups on stu1 and stu2, to find the maximum versions, self-join with the comparison table to retrieve the remaining columns for the corresponding row. It is assumed that the order of stu1 and stu2 does not matter.