Skip to Main Content

Database Software


For appeals, questions and feedback, please email

Support options in ORDER BY clause of MATCH_RECOGNIZE

mathguyJul 31 2020 — edited Jul 31 2020

As documented, MATCH_RECOGNIZE only supports the default ordering by the column or columns listed in the ORDER BY (sub)clause.

In practice, MATCH_RECOGNIZE works (and it works as expected) when ORDER BY is used with the options ASC/DESC and NULLS FIRST/NULLS LAST. ASC NULLS LAST is the default, and the only combination documented  to be supported. In addition, there is at least one issue I am aware of, caused by non-default options in the ORDER BY clause of MATCH_RECOGNIZE.

Namely: if a query uses a non-default ORDER BY in MATCH_RECOGNIZE, and then at the end the query has its own (global) ORDER BY clause using different options, the global ORDER BY is simply ignored. This is discussed in this thread on the SQL and PL/SQL forum:   Weird bug: MATCH_RECOGNIZE followed by ORDER BY, wrong output but only in one particular case

The last reply in that thread is from a forum member who logged this as a bug. It is entirely possible that the bug filing is (or was) dismissed offhand, since the behavior is as documented. If so, then what we need is an enhancement request, which I am submitting here.

Post Details
Added on Jul 31 2020