How do I get most recent row in select statement on Oracle Apex 5?

Question

When the user fills out a form, a column picks up the most recent data from certain columns and concatenates the columns into a new column (the record's id). Now I have the right columns being selected, but the issue I'm having is that for every form that is filled the code only gets the row from the very first record that was ever filled out. Apparently TOP and LIMIT don't work on Oracle Apex 5, and it seems that ROWNUM = 1 is what is causing the problem here.

DECLARE
  IDENTITY VARCHAR2(40);
BEGIN
  SELECT CONCAT(COL1, CONCAT('-', CONCAT(COL2, COL3)))
 INTO IDENTITY FROM TABLE
 WHERE ROWNUM = 1;
 RETURN IDENTITY; 
END;

where rownum = 1 uses the rownum pseudocolumn to return just the first row of the result set. it is doing exactly what is it coded to do. What is it you want to return from the query, you've specified what it is doing but not what you want? — unleashed
Also, for readability, consider the concatenation operator for readability. SELECT col1 || '-' || col2 || col3 ... — unleashed
I want it to get the most recent row as opposed to just getting the first row that is there. — thelamp
"The pipes don't work". Then you are doing it wrong. Why don't they work for you? — unleashed

unleashed unleashed · Accepted Answer · 2017-03-30T14:23:34

You have not specified oracle version. Pre-12c, you have to sort the result first, then return the first row. This is called a Top-N query. You need to supply the search column in the queries below.

DECLARE
  IDENTITY VARCHAR2(40);
BEGIN
 SELECT NEW_ID
 INTO IDENTITY
 FROM (SELECT COL1 || '-' || COL2 || COL3 as NEW_ID
       FROM TABLE
       ORDER BY <sort column> )
 WHERE ROWNUM = 1;
 RETURN IDENTITY; 
END;

If you are using 12c, you can use FETCH FIRST ROW ONLY

DECLARE
  IDENTITY VARCHAR2(40);
BEGIN
 SELECT COL1 || '-' || COL2 || COL3
 INTO IDENTITY
 FROM TABLE
 ORDER BY <sort column>
 FETCH FIRST ROW ONLY;
 RETURN IDENTITY; 
END;

How do I get most recent row in select statement on Oracle Apex 5?

1 Answers