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
);
select
id,
letter
from
tq84_test_data
order by
id
fetch first row only;