Choosing the maximum sum of two columns

Question

I have comparison tables. If I run

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

I get something like:

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

I would like to highlight the entire line where stu1Vers + stu2Vers is the maximum. I keep trying something line by line

 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; 

but this returns a rather random assortment of things:

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

I am trying to get row 392 in the first table.

Answer 1

If you want all rows to have multiple rows with the same maximum value, you can use this query:

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

Including your condition:

 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 

As for your question update, I'm not sure if you want to return all the lines grouped by stu1 and stu2. Perhaps you mean the ordered columns? If yes, add ORDER BY stu1, stu2 to the query.

Answer 2

How about something like:

 SELECT TOP 1 comparisonid, stu1vers, stu2vers, stu1Vers + stu2Vers AS MaxValue FROM comparisons ORDER BY MaxValue DESC 

Answer 3

Have you tried something like this?

  SELECT comparisonID,stu1Vers,stu2Vers,stu1,stu2, max(stu1Vers + stu2Vers) as maximum from comparisons WHERE stu1!=stu2 and assignmentid=9 order by maximum desc limit 1;