0

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

ID PAYMENT METHOD
1 CASH
1 VISA
1 CASH
2 CASH
2 CASH
2 CASH
3 CHEQUE
3 VISA
3 VISA
4 CASH
4 CASH
4 CASH

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:

ID PAYMENT METHOD HAS CHANGED
1 VISA
3 CHEQUE
Improve this question
3
  • Sample data is great, but you also need to specify the expected result. Commented Jan 20, 2022 at 11:03
  • How do you decide to return VISA for ID = 1, and not CASH? Why is CHEQUE returned for ID = 2? Commented Jan 20, 2022 at 11:33
  • MTO's answer is probably the best for how you wrote your question. I have a couple of suggestions to possibly get additional and more specific answers. First, your example output isn't possible. You list ID 2 CHEQUE, however, ID 2 in the table only has CASH. Any answers will be guessing at what you are trying to accomplish. Second, your example output only shows 1 payment method per ID. I assume you want the most recent payment method, however, you have no column to order by. If your table has a timestamp it would allow simpler queries to get the latest changed payment method per ID. Commented Jan 24, 2022 at 7:40

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments