How to get Max of sum of two columns, with associated column - SQL Live

Question

CREATE TABLE emp
(
   empno      NUMBER (4, 0),
   ename      VARCHAR2 (10),
   job        VARCHAR2 (9),
   mgr        NUMBER (4, 0),
   hiredate   DATE,
   sal        NUMBER (7, 2),
   comm       NUMBER (7, 2),
   deptno     NUMBER (2, 0),
   CONSTRAINT pk_emp PRIMARY KEY (empno)
);

This is the table creation, obviously

enter image description here This is the table output

Now what i need to do is find the sum of Sal + Comm for the Salesman rows, and then find the max of those sums, then display that max plus the corresponding ename

Here is my code

     SELECT ename, MAX (SUM (sal + comm)) max_sal
    FROM emp
   WHERE job = 'SALESMAN'
GROUP BY ename

I know this nowhere near correct, but I'm brand new to SQL so I'm very stuck (using SQL live btw)

Answer 1

Try as

 SELECT deptno,
         MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename,
         MAX (sal + comm)
    FROM emp
   WHERE job = 'SALESMAN'
GROUP BY deptno

DENSE_RANK - will aggregate over only those rows with the MAXIMUM first

KEEP will use the MAX function only over these results and not the complete results of your query

Edit

Without dept_no

SELECT MAX (ename) KEEP (DENSE_RANK LAST ORDER BY sal) ename,
       MAX (sal + comm)
  FROM scott.emp
 WHERE job = 'SALESMAN'