Simplify your queries with Oracle's dense_rank

Data persistence for forms created with Form Builder is done through a REST API. You can implement that API, or use one of the provided implementations. Orbeon's implementation for relational databases, for now specifically MySQL and Oracle, always uses inserts instead of doing updates or deletes¹. For this, tables have a timestamp column, and when data is "updated", a new row is added with the current timestamp. So to retrieve data, we need to get the row matching a condition that has the latest timestamp.

Let's transpose this problem to an equivalent one on the emp table, to make it easier for you to run the queries below in your own database². On the emp table, a similar problem is to find for each job, the employee with the most recent hire date³. This can be done with nested queries:

select e1.* 
  from emp e1,
       (  select job, max(hiredate) hiredate 
            from emp 
        group by job ) e2
 where e1.job = e2.job
       and e1.hiredate = e2.hiredate ;

A bit complicated isn't it? This is where Oracle's dense_rankcomes in. This analytic function allows you to rank data. The following adds a rank column, and for each job, the employee with the latest hire date will get a rank of 1, the employee hire just before that a rank of 2, and so on.

select e.*,
    dense_rank() over (partition by job order by hiredate desc) rank
from emp e;

From here, we just need to keep only the employee with rank = 1 to get the expected result:

select * from
    (select e.*,
            dense_rank() over
            (partition by job order by hiredate desc) rank
       from emp e)
 where rank = 1 ;

¹ This allows auditing and allows an admin to retrieve older version of the data if necessary.
² In Oracle, this table is normally found in the scott schema, and you don't have it, or would like to import it in another database, you can download the SQL to create the table and add data.
³ For the purpose of this discussion, we'll assume there is only one employee for each job with the most recent hire date.