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 tough to learn while you see it for the primary time. Consider it as a sophisticated approach to say you wish 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 once we order the group contents by the ORDER BY clause.

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

Now, there’s fairly a little bit of repetition while you’re projecting a number of columns per group like that. Window capabilities have a WINDOW clause, the place frequent window specs will be named for repeated use. However GROUP BY doesn’t have such a function, most likely as a result of solely few circumstances come up the place this could be helpful.

However fortunately, Oracle has:

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

With these two utilities, we will 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;

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

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

That is only a workaround, after all. It’s tedious to handle named OBJECT varieties like that for each case of aggregation. If you happen to don’t want the sort security, you may all the time additionally simply use JSON as an alternative:

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