Select one row with several aggregate function-based conditions without subquery

Question

I'm not sure if there's more elegant way to solve this than user order by and rownum (in Oracle), but here goes:

I have this table (called winner):

Name     Salary     House   Kids
--------------------------------
Barabara 2500.00    40      4
Dale     2000.00    60      3
Aaron    2000.00    45      2
Joe      2000.00    45      4
Jacob    2000.00    50      4
Melissa  2500.00    50      4

house means house size in sq.m.

I need a query (without subquery) that will find a "winner" (the name, but the row can be with more columns). the "winner" is defined like so :

• earns the lowest salary
• (in case of tie:) has the smallest house
• (in case of another tie:) has the largest number of kids.

In this example, the winner is Joe who earns 2000, has house size of 45, and has 4 kids.

The only way (which has a very simple but efficient code with indices only, and still has a kind of subquery (inline), i.e. has another select) is:

select * 
from 
    (select name 
     from winner
     order by salary, house, kids desc)
where rownum = 1;

Is there another way to do so without subquery ? (maybe aggregate functions?)

Answer 1

In earlier versions of Oracle, you can use keep:

select max(name) keep (dense_rank first order by salary, house, kids desc) as name,
       max(salary) keep (dense_rank first order by salary, house, kids desc) as name,
       max(house) keep (dense_rank first order by salary, house, kids desc) as name,
       max(kids) keep (dense_rank first order by salary, house, kids desc) as name
from rownum_order_test;

If you want ties, you can also try:

select listagg(name, ',') keep (dense_rank first order by salary, house, kids desc) as names 
from rownum_order_test;

This construct can be quite useful when working with correlated subqueries, because of the limited scoping for correlation clauses in Oracle. Note that when getting values

As mentioned by Bulat, there is also a better method in Oracle 12c+ using fetch first 1 row only. And, there is another method using first_value() and select distinct. Neither of these offer a solution for ties.