Microsoft Access FIRST () Equivalent in Oracle SQL

by

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.

  • 3

    please provide some sample data and desired output.

    – 

  • 1

    Without an ORDER BY clause FIRST() or LAST() don’t make any sense. Voting to close.

    – 




  • Don’t expect Oracle people to know MS Access too. Show us an example, a minimal reproducible example.

    – 




  • Is this actually what you want to do? From the docs: ” These functions return the value of a specified field in the first or last record, respectively, of the result set returned by a query. If the query does not include an ORDER BY clause, the values returned by these functions will be arbitrary because records are usually returned in no particular order.”

    – 

You can try MIN(...) KEEP (DENSE_RANK FIRST ORDER BY ...). Since you don’t specify an ORDER BY in your query then you could use ROWNUM to just get whichever row happens to be read first from the table:

SELECT ID,
       MIN("LEVEL")     KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS "LEVEL",
       MIN(NAME)        KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS name,
       MIN(DESIGNATION) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS designation
FROM   "TABLE"
GROUP BY
       ID
HAVING MIN("LEVEL")     KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) <> '1';

Which, for the sample data:

CREATE TABLE "TABLE" (ID, "LEVEL", name, designation) AS
SELECT 1, 'A', 'Alice', 'AA' FROM DUAL UNION ALL
SELECT 1, 'B', 'Beryl', 'BB' FROM DUAL UNION ALL
SELECT 1, 'C', 'Carol', 'CC' FROM DUAL UNION ALL
SELECT 2, 'E', 'Emily', 'EE' FROM DUAL UNION ALL
SELECT 2, 'D', 'Debra', 'DD' FROM DUAL UNION ALL
SELECT 3, 'F', 'Fiona', 'FF' FROM DUAL;

Outputs:

ID LEVEL NAME DESIGNATION
1 A Alice AA
2 E Emily EE
3 F Fiona FF

fiddle

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';

Leave a Comment