Oracle SQL Query without using multiple inline selects

Question

Env: Oracle 12c

I have a table called: MY_TAB with the following sample data:

CODE         KEY_ID
------------ --------
2000         95
1055         96
2000         97

Using the above sample table data, I need to take the minimum CODE value, in this instance 1055 together with the KEY_ID alongside that minimum CODE value, i.e. 96 and use it within another query as such:

select *
from   another_table
where  code = minimum CODE value from MY_TAB
and    key_id = KEY_ID alongside the minimum CODE value

Was thinking to use something like this:

where code = (select min(CODE) from MY_TAB where .....) 1055
and   key_id = select KEY_ID from MY_TAB for MIN(CODE)  96

Unsure what the best way to approach this query without having multiple nested inline selects.

Answer 1

I would recommend:

select code, key_id
from my_tab
order by code
fetch first 1 row only

You can use this in a query as:

where (t.code, t.key_id) in (select code, key_id
                             from my_tab
                             order by code
                             fetch first 1 row only
                            )

An alternative in older versions of Oracle is:

select min(code) as code,
       min(key_id) keep (dense_rank first order by code) as key_id
from my_tab;