Using ORDER BY and FOR UPDATE in a single query

I'm trying to fetch new messages from a table TXINCH that have an entry in another table TXINCH_FILE, and return the output sorted by the column M_PRIORITY from the table RTPE_CNB_FILES. They are joined by a file id/file reference column.

RTPE_CNB_FILES and TXINCH_FILE contain the imported batch files while TXINCH contains the individual transactions in each file, which can be a couple thousand.

-- Get file with the highest priority that also has an entry in TXINCH_FILE

WITH next_file_id AS (
    SELECT *
    FROM (
        SELECT f.TXFILEID
        FROM TXINCH_FILE f
        LEFT OUTER JOIN RTPE_CNB_FILES cnbf
        ON f.TXFILEID = cnbf.M_FILE_REF
        WHERE f.TXSTATE = 'NW'
        ORDER BY cnbf.M_PRIORITY ASC
    )
    WHERE ROWNUM <= 1
)

-- Process the entries in TXINCH using the previously fetched file id

SELECT *
FROM TXINCH i
WHERE i.TXSTATE = 'NW'
  AND i.TXFILEID = (
    SELECT TXFILEID
    FROM next_file_id
    WHERE ROWNUM <= 1
  )
  AND ROWNUM <= 750
FOR UPDATE SKIP LOCKED

This query fetches only one file reference at a time, which would be fine if each file had more than 750 transactions. However, if I get a high number of files with one transaction each, the framework causes a delay of 15 seconds per query.

I'm looking for a single query that can fetch entries while sorting them by priority ('HIGH', 'LOW' or (null)) while locking the rows as they are in use. I tried ORDER BY clauses but that causes the ORA-02014 error:

cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

I'd need to choose HIGH priorities if there are any, use LOW or empty otherwise or fetch as many rows as I can (ignoring the ROWNUM <= 750 at the end) while keeping the priority order.

CREATE TABLE RTPE_CNB_FILES (
    M_FILE_REF VARCHAR2(32) NOT NULL,
    M_PRIORITY VARCHAR2(8) NOT NULL
);

CREATE TABLE TXINCH_FILE (
  TXFILEID VARCHAR2(32) NOT NULL,
  TXSTATE VARCHAR2(8)

);

CREATE TABLE TXINCH (
    TXREF VARCHAR2(32) NOT NULL,
    TXFILEID VARCHAR2(32) NOT NULL,
    TXSTATE VARCHAR2(8)
);

INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES("0001","HIGH");
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES("0002","HIGH");
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES("0003","HIGH");
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES("0004","LOW");
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES("0005","LOW");
INSERT INTO RTPE_CNB_FILES (M_FILE_REF, M_PRIORITY) VALUES("0006","LOW");

INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES("0001", "NW");
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES("0002", "NW");
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES("0003", "NW");
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES("0004", "NW");
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES("0005", "NW");
INSERT INTO TXINCH_FILE (TXFILEID, TXSTATE) VALUES("0006", "NW");

INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("1","0001", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("2","0001", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("3","0001", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("4","0002", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("5","0002", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("6","0003", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("7","0003", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("8","0003", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("9","0003", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("10","0004", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("11","0004", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("12","0004", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("13","0004", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("14","0005", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("15","0005", "NW");
INSERT INTO TXINCH (TXREF, TXFILEID, TXSTATE) VALUES("16","0006", "NW");

With the database in this state the query returns only three rows; the three entries in TXINCH that have M_PRIORITY 'HIGH'. It should also return the other rows with M_PRIORITY 'HIGH' without being limited to a single file reference (a single entry in RTPE_CNB_FILES) per query.

Current output:

Target output:

The situation I expect is that the query also returns the other rows with prio 'HIGH' without being limited to a single file ref (a single entry in RTPE_CNB_FILES) per query.

From Oracle 12, you can find all the m_file_ref with the highest priority using:

SELECT M_FILE_REF
FROM   RTPE_CNB_FILES
ORDER BY
       CASE M_PRIORITY
       WHEN 'HIGH' THEN 1
       WHEN 'LOW'  THEN 2
                   ELSE 3
       END
FETCH FIRST ROW WITH TIES

You can then use that to filter the rows of TXINCH:

SELECT *
FROM   TXINCH
WHERE  TXSTATE = 'NW'
AND    txfileid IN (
  SELECT M_FILE_REF
  FROM   RTPE_CNB_FILES
  ORDER BY
         CASE M_PRIORITY
         WHEN 'HIGH' THEN 1
         WHEN 'LOW'  THEN 2
                     ELSE 3
         END
  FETCH FIRST ROW WITH TIES
)

In earlier versions, you can use the DENSE_RANK (or RANK) analytic function:

SELECT *
FROM   TXINCH
WHERE  TXSTATE = 'NW'
AND    (txfileid, 1) IN (
  SELECT M_FILE_REF,
         DENSE_RANK() OVER (
           ORDER BY CASE M_PRIORITY
                    WHEN 'HIGH' THEN 1
                    WHEN 'LOW'  THEN 2
                                ELSE 3
                    END
         )
  FROM   RTPE_CNB_FILES
)

Which, for the sample data, both output:

fiddle