I want to find if the value changed for a particular ID

For example, ID 1 and ID 3 changed their payment method and I want to write a query that can detect that change, thanks in advance

example for expected output:

CodePudding user response：

You can use:

SELECT id,
       MIN(payment_method) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM)
         AS payment_method
FROM   table_name
GROUP BY id
HAVING COUNT(DISTINCT payment_method) > 1

or:

SELECT *
FROM   (
  SELECT id,
         payment_method,
         ROWNUM AS ordering_column
  FROM   table_name t
)
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER     BY ordering_column
  MEASURES
    FIRST(payment_method) AS payment_method
  PATTERN (^ same  diff)
  DEFINE
    same AS FIRST(payment_method) = payment_method,
    diff AS diff.payment_method <> same.payment_method
);

(Note: ROWNUM should be replaced by a column that will identify a deterministic ordering in the rows for each ID such as a timestamp column; however, such a column is not present in your sample data so it cannot be used. ROWNUM will just number the rows in the order that the SQL engine processes them and that processing order may be non-deterministic.)

Which both output:

ID	PAYMENT_METHOD
1	CASH
3	CHEQUE

db<>fiddle here