Search notes:

Oracle MATCH_RECOGNIZE: Select first row

The following example demonstrates how match_recognize can be «abused» to select the «first» record in a table or resultset.

First, we need some test data:

create table tq84_test_data (id integer, letter char(1));
insert into tq84_test_data
select
   level,
   chr(64+level)
from
   dual connect by level < 10;

In the actual query, we define the pattern variable ptrn that matches each row (as 1=1).

Then, in the pattern clause, we use the start (ptrn*) to match as many rows as possible, i. e. the entire result set.

Finally, we apply first(…) in the measures clause to return values of the «first» (matched) record:

select
   *
from
   tq84_test_data
match_recognize (
   order by id
   measures
      first(id    ) as id,
      first(letter) as letter
   pattern (ptrn*)
   define
      ptrn as 1=1
);

Of course, the same result can be queried with the fetch first row only clause way easier:

select
   id,
   letter
from
   tq84_test_data
order by
   id
fetch first row only;

Index