By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (ID, Salary) As
SELECT 1, 90 FROM DUAL UNION ALL
SELECT 2, 78 FROM DUAL UNION ALL
SELECT 3, 68 FROM DUAL UNION ALL
SELECT 4, 67 FROM DUAL UNION ALL
SELECT 5, 62 FROM DUAL;
5 rows affected
SELECT MIN(outcome) AS outcome
FROM table_name
MATCH_RECOGNIZE(
ORDER BY id
MEASURES
CLASSIFIER() AS outcome
ALL ROWS PER MATCH
PATTERN ( ^ (FALSE|TRUE)+ $ )
DEFINE
FALSE AS PREV(salary) < salary,
TRUE AS PREV(salary) >= salary OR PREV(salary) IS NULL
)
| OUTCOME |
|---|
| TRUE |
SELECT MIN(outcome) AS outcome
FROM (
SELECT CASE
WHEN salary <= LAG(salary, 1, salary) OVER (ORDER BY id)
THEN 'TRUE'
ELSE 'FALSE'
END AS outcome
FROM table_name
)
| OUTCOME |
|---|
| TRUE |
DROP TABLE table_name;
CREATE TABLE table_name (ID, Salary) As
SELECT 1, 90 FROM DUAL UNION ALL
SELECT 2, 78 FROM DUAL UNION ALL
SELECT 3, 68 FROM DUAL UNION ALL
SELECT 4, 97 FROM DUAL UNION ALL
SELECT 5, 62 FROM DUAL;
5 rows affected
SELECT MIN(outcome) AS outcome
FROM table_name
MATCH_RECOGNIZE(
ORDER BY id
MEASURES
CLASSIFIER() AS outcome
ALL ROWS PER MATCH
PATTERN ( ^ (FALSE|TRUE)+ $ )
DEFINE
FALSE AS PREV(salary) < salary,
TRUE AS PREV(salary) >= salary OR PREV(salary) IS NULL
)
| OUTCOME |
|---|
| FALSE |
SELECT MIN(outcome) AS outcome
FROM (
SELECT CASE
WHEN salary <= LAG(salary, 1, salary) OVER (ORDER BY id)
THEN 'TRUE'
ELSE 'FALSE'
END AS outcome
FROM table_name
)
| OUTCOME |
|---|
| FALSE |