选择两列之和的最大值

我有一个表格比较。如果我跑

SELECT comparisonID,stu1Vers,stu2Vers,stu1,stu2 
    from comparisons 
    WHERE stu1!=stu2 and assignmentid=9;

我得到类似的东西：

+--------------+----------+----------+------+------+
| comparisonID | stu1Vers | stu2Vers | stu1 | stu2 |
+--------------+----------+----------+------+------+
|          287 |       12 |        2 |    1 |    6 |
|          286 |       12 |        1 |    1 |    6 |
|          276 |       11 |        2 |    1 |    6 |
|          275 |       11 |        1 |    1 |    6 |
|          266 |       10 |        2 |    1 |    6 |
|          265 |       10 |        1 |    1 |    6 |
|          257 |        9 |        2 |    1 |    6 |
|          256 |        9 |        1 |    1 |    6 |
...
|          391 |       19 |        1 |    1 |    6 |
|          392 |       19 |        2 |    1 |    6 |
+--------------+----------+----------+------+------+

我想选择 Stu1Vers+stu2Vers 最大的整行。我一直在尝试一些类似的事情

select c.comparisonid,c.stu1vers,c.stu2vers,max(totvers) 
from comparisons as c join 
    (select comparisonid, stu1vers+stu2vers as totvers 
    from comparisons where stu1!=stu2 group by comparisonid) as cm 
on c.comparisonid = cm.comparisonid and c.stu1vers+c.stu2vers = cm.totvers;

但这会返回相当随机的各种东西：

+--------------+----------+----------+--------------+
| comparisonid | stu1vers | stu2vers | max(totvers) |
+--------------+----------+----------+--------------+
|          220 |        1 |        1 |           21 |
+--------------+----------+----------+--------------+

我正在尝试获取第一个表中的第 392 行。

如果您想要多行具有相同最大值时的所有行，那么您可以使用以下查询：

SELECT * FROM Table1
WHERE stu1Vers + stu2Vers = (SELECT MAX(stu1Vers + stu2Vers) FROM Table1)

包括你的情况：

SELECT * FROM Table1
WHERE stu1Vers + stu2Vers = (
    SELECT MAX(stu1Vers + stu2Vers)
    FROM Table1
    WHERE stu1!=stu2 and assignmentid=9
) AND stu1!=stu2 and assignmentid=9

Result:

392, 19, 2, 1, 6

关于您对问题的更新，我不确定您返回按 Stu1 和 Stu2 分组的所有行是什么意思。也许您的意思是按这些列排序？如果是这样，请添加ORDER BY stu1, stu2到查询。