How can I SELECT a row with MAX(value) from a derived table where the values are all calculated sums?

Question

I have tried the following code but I just get the full table with all the names and sum values instead of one row with the max value:

SELECT stageName, max(total_salary)
FROM (
      SELECT c.*, sum(p.dailySalary) as total_salary        
      from contender as c
      left join participant as p
      on (p.contender = c.idContender)
      group by c.idContender ) b
group by stageName;

output: 
Yellow Jesters  205
TikTok  3073
Teabags 947
Bobbleheads 11840
Reddit  1486

but I just need: Bobbleheads 11840

PS: Please suggest a solution WITHOUT using desc and limit

If you are using MySQL 8.0, you can use [window functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions.html). — Bill Karwin, Mar 27 '20 at 22:53
@BillKarwin could you please show me how to do it without windows functions and desc and limit? — cuffty, Mar 27 '20 at 23:20
At this point you should list the SQL features you _are_ allowed to use. I'm not going to play a guessing game. — Bill Karwin, Mar 27 '20 at 23:29
@BillKarwin yea sure. I need to make a select query using joins, aggregate functions and basic functions like sum and max. — cuffty, Mar 27 '20 at 23:32

score 1 · Answer 1 · answered Mar 27 '20 at 22:51

1

If you just want to top row in your resultset, you can sort and limit:

select c.*, sum(p.dailySalary) as total_salary        
from contender as c
left join participant as p on p.contender = c.idContender
group by c.idContender
order by total_salary desc
limit 1

If there is a possibility of top tie, and you want to allow it, you can use window functions:

select *
from (
    select 
        c.*, 
        sum(p.dailySalary) as total_salary, 
        rank() over(order by sum(p.dailySalary) desc) rn
    from contender as c
    left join participant as p on p.contender = c.idContender
    group by c.idContender
) t
where rn = 1

answered Mar 27 '20 at 22:51

GMB

216,147
25
84
135

I am sorry but could you suggest a solution wihtout using desc (and windows functions) like I mentioned in my ps? Thank you so much – cuffty Mar 27 '20 at 23:04
@mystictuff: why do you want to avoid `order by` and `limit` (as shown in the first query)? This seems like the most relevant option here (and all versions of MySQL support this). – GMB Mar 27 '20 at 23:06
my assignment disallows those options which is why I've been struggling with it for so long. Could you please help me out? – cuffty Mar 27 '20 at 23:07
@mystictuff: I don't see the rationale for these constraints - and you did not express them in your original question. As far as concerned, I provided two reasonable queries that give you the results that you expect. – GMB Mar 27 '20 at 23:30

score 0 · Accepted Answer · answered Mar 28 '20 at 00:01

Here's a solution that should work on any version of MySQL 5.x, using no ORDER BY, LIMIT, window functions, views, or CTEs.

SELECT a.stagename, a.total_salary
FROM (
      SELECT c.*, sum(p.dailySalary) as total_salary        
      from contender as c
      left join participant as p
      on (p.contender = c.idContender)
      group by c.idContender ) AS a
LEFT OUTER JOIN (
      SELECT c.*, sum(p.dailySalary) as total_salary        
      from contender as c
      left join participant as p
      on (p.contender = c.idContender)
      group by c.idContender ) AS b
  ON a.total_salary < b.total_salary
WHERE b.total_salary IS NULL;

Tested on MySQL 5.7.27.

Output:

+-------------+--------------+
| stagename   | total_salary |
+-------------+--------------+
| Bobbleheads |        11840 |
+-------------+--------------+

How can I SELECT a row with MAX(value) from a derived table where the values are all calculated sums?

2 Answers2