I want to find out if the PAYMENT METHOD has been changed for a particular ID from the last time he pays.

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 outputs (I want to have a table that contains the changed payment method):

CodePudding user response：

You just need simple aggregation with having clause: DBFiddle

select
   id, 
   max(payment_method)
     keep(dense_rank last order by dt) as LAST_PAYMENT_METHOD
from t
group by id
having count(distinct payment_method)>1;

Full example with test data:

with t(ID, PAYMENT_METHOD, DT) as (
select 1, 'CASH'  , to_date(2022,'YYYY') from dual union all
select 1, 'VISA'  , to_date(2021,'YYYY') from dual union all
select 1, 'CASH'  , to_date(2020,'YYYY') from dual union all
select 2, 'CASH'  , to_date(2021,'YYYY') from dual union all
select 2, 'CASH'  , to_date(2020,'YYYY') from dual union all
select 2, 'CASH'  , to_date(2019,'YYYY') from dual union all
select 3, 'CHEQUE', to_date(2021,'YYYY') from dual union all
select 3, 'VISA'  , to_date(2020,'YYYY') from dual union all
select 3, 'VISA'  , to_date(2019,'YYYY') from dual union all
select 4, 'CASH'  , to_date(2021,'YYYY') from dual union all
select 4, 'CASH'  , to_date(2020,'YYYY') from dual union all
select 4, 'CASH'  , to_date(2019,'YYYY') from dual
)
select
   id, 
   max(payment_method)
     keep(dense_rank last order by dt) as LAST_PAYMENT_METHOD
from t
group by id
having count(distinct payment_method)>1;

Output:

        ID LAST_PAYMENT_METHOD
---------- --------------------
         1 CASH
         3 CHEQUE

CodePudding user response：

You can use the row_number() and count() analytic functions:

with u as
(select ID,
"PAYMENT METHOD",
row_number() over(partition by ID order by "DATE" desc) as rn,
count(distinct "PAYMENT METHOD") over(partition by ID) as cnt
from table_name)
select ID, "PAYMENT METHOD" from u
where rn = 1 and cnt >= 2

CodePudding user response：

Here's one option:

SQL> with temp as
  2    (select id,
  3            payment_method,
  4            row_number() over (partition by id order by year desc) rn
  5     from pay a
  6     where exists (select b.id
  7                   from pay b
  8                   where b.id = a.id
  9                   group by b.id
 10                   having count(distinct payment_method) > 1
 11                  )
 12    )
 13  select id, payment_method
 14  from temp
 15  where rn = 1;

        ID PAYMEN
---------- ------
         1 CASH
         3 CHEQUE

SQL>

• the exists subquery finds IDs whose payment_method has changed
• the temp CTE "sorts" rows per each id, ordered by year in descending order (which means that rn = 1 represents the last year)
• the final select just returns the highest ranked row for IDs which changed payment method