CODEWITHSUNDEEP
sqlmysql
Kyle Schmidt
Kyle Schmidt

Reputation: 318

Selecting max of a sum of two columns

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.

Upvotes: 3

Views: 8024

Answers (3)

Mark Byers
Mark Byers

Reputation: 838336

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.

Upvotes: 3

Cetra
Cetra

Reputation: 2621

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;

Upvotes: 0

mythz
mythz

Reputation: 143339

How about something like:

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

Upvotes: 2

Related Questions