Selecting max of a sum of two columns

Question

I have a table comparisons. 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'd like to select the entire row where stu1Vers+stu2Vers is the maximum. I keep trying something along the lines of

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 that returns a rather random assortment of things:

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

I'm trying to get row 392 in the first table.

What should happen if there are multiple rows with the same maximum value? Return all of them, or just one? If the latter, which one? — Mark Byers
– Mark Byers, Commented Feb 17, 2010 at 0:28
There's actually another two columns in the table, one each for stu1 and stu2's ids. So return all of them, but grouped by stu1 or stu2). max(stu1vers+stu2vers) is guaranteed to be unique for any student for any assignment. — Kyle Schmidt
– Kyle Schmidt, Commented Feb 17, 2010 at 0:34

Mark Byers · Accepted Answer · 2010-02-17 00:38:39Z

3

If you want all the rows when there are multiple rows with the same maximum value, then 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

Regarding your update to the question, I'm not sure what you mean to return all the rows grouped by stu1 and stu2. Perhaps you mean ordered by these columns? If so, add ORDER BY stu1, stu2 to the query.

edited Feb 17, 2010 at 0:38

answered Feb 17, 2010 at 0:32

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mythz · Accepted Answer · 2010-02-17 00:33:01Z

2

How about something like:

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

answered Feb 17, 2010 at 0:33

mythz

144k31 gold badges253 silver badges400 bronze badges

2 Comments

Mark Byers Over a year ago

Note: This will only return one of the matches, not all of them as requested in the update to the question.

Mark Byers Over a year ago

And TOP 1 is not how you return one row in MySQL.

Cetra · Accepted Answer · 2010-02-17 00:36:20Z

0

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;

answered Feb 17, 2010 at 0:36

Cetra

2,6111 gold badge22 silver badges27 bronze badges

Collectives™ on Stack Overflow

Selecting max of a sum of two columns

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related