在sql中为两个派生表使用联接

Question

基本上，我有两个单独的查询，我需要以某种方式将它们合并到一组结果中。

！[这是表1，显示了各组工资的总和]1

下面是我为形成表而编写的查询。

SELECT con_stagename, SUM(p_daily_salary) AS sum_salary
                FROM CONTENDER, PARTICIPANT
                WHERE p_contender = con_id
                GROUP BY con_id;
                
SELECT MAX(sum_salary) AS max_salary
FROM (SELECT con_stagename, SUM(p_daily_salary) AS sum_salary
                FROM CONTENDER, PARTICIPANT
                WHERE p_contender = con_id
                GROUP BY con_id) T2;

问题是，如果我希望结果是一个单行值，其中有最高工资的组的名称，和实际金额。我该怎么做？我一直在尝试使用连接操作，但没有运气。

Answer 1

SELECT con_stagename, SUM(p_daily_salary) AS sum_salary
FROM CONTENDER, PARTICIPANT
WHERE p_contender = con_id
GROUP BY con_id
ORDER BY 2 DESC
LIMIT 1