# How to Get a Non-Aggregate value in an Aggregate Query

Suppose you want to find the maximum salary for each job in the `emp` table. This is very easy using using the `max` aggregate:

```sql
select job, max(sal) max_sal
from emp
group by job
order by job
;

JOB       MAX_SAL 
--------- ------- 
ANALYST      3000 
CLERK        1300 
MANAGER      2975 
PRESIDENT    5000 
SALESMAN     1600
```

The next logical question is: *Who earns the max salary?* A very common solution to this problem is to use an analytical function:

```sql
select
    job, ename, sal
from (
    select
        row_number() over (partition by job order by sal desc) rn,
        job, ename, sal
    from emp
)
where rn = 1
order by job
;

JOB       ENAME   SAL 
--------- ------ ---- 
ANALYST   SCOTT  3000 
CLERK     MILLER 1300 
MANAGER   JONES  2975 
PRESIDENT KING   5000 
SALESMAN  ALLEN  1600 

-- Explain plan:                                                                                     
------------------------------------------------------------------------------------ 
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT            |      |    14 |   546 |     4  (25)| 00:00:01 | 
|*  1 |  VIEW                       |      |    14 |   546 |     4  (25)| 00:00:01 | 
|*  2 |   WINDOW SORT PUSHED RANK   |      |    14 |   252 |     4  (25)| 00:00:01 | 
|   3 |    TABLE ACCESS STORAGE FULL| EMP  |    14 |   252 |     3   (0)| 00:00:01 | 
------------------------------------------------------------------------------------ 
                                                                                     
Predicate Information (identified by operation id):                                  
---------------------------------------------------                                  
                                                                                     
   1 - filter("RN"=1)                                                                
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "JOB" ORDER BY "SAL")<=1)
```

There is an alternate way to do this using the [`keep`](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/FIRST.html#GUID-85AB9246-0E0A-44A1-A7E6-4E57502E9238) clause and `dense_rank` along with the `first/last` commands:

```sql
select
    job,
    -- The initial "min" aggregate could also be "max" 
    -- The key logic is in the dense_rank first/last and ordering
    min(ename) keep (dense_rank last order by sal) ename,
    -- Could also write as:
    -- min(ename) keep (dense_rank first order by sal desc) ename,
    max(sal)
from emp
group by job
order by job
;

JOB       ENAME  MAX(SAL) 
--------- ------ -------- 
ANALYST   FORD       3000 
CLERK     MILLER     1300 
MANAGER   JONES      2975 
PRESIDENT KING       5000 
SALESMAN  ALLEN      1600 

-- Explain Plan
----------------------------------------------------------------------------------- 
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT           |      |     5 |    90 |     4  (25)| 00:00:01 | 
|   1 |  SORT GROUP BY             |      |     5 |    90 |     4  (25)| 00:00:01 | 
|   2 |   TABLE ACCESS STORAGE FULL| EMP  |    14 |   252 |     3   (0)| 00:00:01 | 
-----------------------------------------------------------------------------------
```

Using the `dense_rank` command is easier to write and it may result in more performant queries. The different explain plans shows that less bytes were required.

*Note: the keen observer may note that for the* `ANALYST` *position there's two different results. That's because both they're two analysts (*`FORD` *and* `SCOTT`*) and they both earn the same amount. If "ties are allowed" the following query can be used:*

```sql
select job, ename, sal
from (
    select 
        job, ename, sal,
        dense_rank() over (partition by job order by sal desc) rn
    from emp
)
where rn = 1
order by job
;

-- Note that ANALYST has two rows given that SCOTT and FORD earn the same amount
JOB       ENAME   SAL 
--------- ------ ---- 
ANALYST   SCOTT  3000 
ANALYST   FORD   3000 
CLERK     MILLER 1300 
MANAGER   JONES  2975 
PRESIDENT KING   5000 
SALESMAN  ALLEN  1600
```

## References

* Oracle's official documentation on this feature is actually in the [`first`](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/FIRST.html#GUID-85AB9246-0E0A-44A1-A7E6-4E57502E9238) command
    
* [Rob van Wijk](https://twitter.com/rwijk) has a good [article](https://rwijk.blogspot.com/2012/09/keep-clause.html) about the `keep` clause
    
    * Covers some of the performance gains in detail
        

*Thanks to* [*Jeff Kemp*](https://twitter.com/jeffreykemp) *for showing me this feature!*