Oracle’s solution to get a number of values in a high 1 per group question

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

An Oracle particular model in that put up 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, rely(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 whenever you see it for the primary time. Consider it as a sophisticated solution 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 in case you don’t embrace a singular worth within the ORDER BY clause. In that case, you may mixture all of the ties, e.g. to get an AVG() if that is sensible in what you are promoting case. When you 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 whenever you’re projecting a number of columns per group like that. Window features have a WINDOW clause, the place widespread window specs may be named for repeated use. However GROUP BY doesn’t have such a characteristic, in all probability as a result of solely few circumstances come up the place this may be helpful.

However fortunately, Oracle has:

• OBJECT varieties, that are simply nominally typed row worth expressions
• ANY_VALUE, an mixture operate 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;

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

ORA-22950: can’t order objects with out MAP or ORDER methodology

That is only a workaround, after all. It’s tedious to handle named OBJECT varieties like that for each case of aggregation. When you don’t want the kind 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;