Microsoft Access FIRST () Equivalent in Oracle SQL

Question

I have a simple query in MS Access database

SELECT
ID,
FIRST(LEVEL),
FIRST(NAME),
FIRST(DESIGNATION)
FROM TABLE 1
WHERE FIRST(LEVEL)<> '1';

What is the Oracle SQL equivalent of the FIRST() expression.

Answer 1

Something like this might be one option:

• use a CTE to fetch all columns you need, along with row_number analytic function which partitions data on id and sorts them by ... what, exactly? What is "first" in your case? I chose id; if it is not, use column you really want
• then, in another CTE, use max aggregate function with a condition which says that only value - whose row number equals 1 - should be returned
• finally, filter data

Note that you can't (OK, you can, but probably shouldn't) name column level in Oracle as it is reserved for pseudocolumn. That's why I used c_level.

with 
temp as
  (select id, c_level, name, designation,
     row_number() over (partition by id order by id) rn
   from table_1
  ),
temp2 as
  (select id,
     max(case when rn = 1 then c_level end) c_level,
     max(case when rn = 1 then name end) name,
     max(case when rn = 1 then designation end) designation
   from temp
   group by id)
select id, c_level, name, designation
from temp
where c_level <> '1';