Oracle’s approach to get a number of values in a prime 1 per group question

I’ve blogged about generic methods of getting prime 1 or prime n per class queries earlier than on this weblog.

An Oracle particular model in that submit used the arcane KEEP syntax:

SELECT
  max(actor_id)   KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
  max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
  max(last_name)  KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
  max(c)          KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id)
FROM (
  SELECT actor_id, first_name, last_name, depend(film_id) c
  FROM actor
  LEFT JOIN film_actor USING (actor_id)
  GROUP BY actor_id, first_name, last_name
) t;

It is a bit troublesome to learn if you see it for the primary time. Consider it as a sophisticated approach to say you need to get the primary worth per group. This hypothetical syntax could be a lot nicer:

SELECT
  FIRST(actor_id ORDER BY c DESC, actor_id),
  FIRST(first_name ORDER BY c DESC, actor_id),
  FIRST(last_name ORDER BY c DESC, actor_id),
  FIRST(c ORDER BY c DESC, actor_id)
FROM (...) t;

So, we’re getting the FIRST worth of an expression per group after we order the group contents by the ORDER BY clause.

Oracle’s syntax takes under consideration that ordering could also be non-deterministic, resulting in ties when you don’t embody a novel worth within the ORDER BY clause. In that case, you possibly can combination all of the ties, e.g. to get an AVG() if that is sensible in your corporation case. Should you don’t care about ties, or guarantee there are not any ties, MAX() is an OK workaround, or since 21c, ANY_VALUE()

Now, there’s fairly a little bit of repetition if you’re projecting a number of columns per group like that. Window capabilities have a WINDOW clause, the place widespread window specs might be named for repeated use. However GROUP BY doesn’t have such a function, in all probability as a result of solely few instances come up the place this might be helpful.

However fortunately, Oracle has:

• OBJECT varieties, that are simply nominally typed row worth expressions
• ANY_VALUE, an combination operate that generates any worth per group, which has been added in Oracle 21c

With these two utilities, we are able to do that:

CREATE TYPE o AS OBJECT (
  actor_id NUMBER(18),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  c NUMBER(18)
);

And now:

SELECT
  ANY_VALUE(o(actor_id, first_name, last_name, c))
    KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id)
FROM (...) t;

Notice, it will be potential to make use of MAX() in older Oracle variations, when you work round this error message as effectively:

ORA-22950: can not order objects with out MAP or ORDER technique

That is only a workaround, in fact. It’s tedious to handle named OBJECT varieties like that for each case of aggregation. Should you don’t want the sort security, you possibly can at all times additionally simply use JSON as a substitute:

SELECT
  ANY_VALUE(JSON_OBJECT(actor_id, first_name, last_name, c))
    KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id)
FROM (...) t;