For the given table below, how can we find the customer whose name appears 3 times consecutively.
+---------+-----------+
| CUST_ID | CUST_NAME |
+---------+-----------+
| 1 | SAM |
+---------+-----------+
| 2 | SAM |
+---------+-----------+
| 3 | SAM |
+---------+-----------+
| 4 | PETER |
+---------+-----------+
| 5 | PETER |
+---------+-----------+
Desired_Output
+-----------+
| CUST_NAME |
+-----------+
| SAM |
+-----------+
Table Definition:
create table Customer
(
cust_id int,
cust_name varchar2(20)
);
insert into customer values (1, 'SAM');
insert into customer values (2, 'SAM');
insert into customer values (3, 'SAM');
insert into customer values (4, 'PETER');
insert into customer values (5, 'PETER');
Code Tried so far
Select distinct cust_name from (
select
cust_id,
cust_name,
lag(cust_name,1,0) over (order by cust_id) as prev_cust_name,
lead(cust_name,1,0) over (order by cust_id) as next_cust_name
from customer) a
where a.prev_cust_name=a.next_cust_name;
I believe we can do this by using lead/lag to get the previous and next row. Although my solution gives the desired output but i don't think this is correct solution.
Your method is close. You need one more comparison:
select distinct cust_name
from (select c.*
lag(cust_name) over (order by cust_id) as prev_cust_name,
lead(cust_name) over (order by cust_id) as next_cust_name
from customer c
) a c
where prev_cust_name = cust_name and cust_name = next_cust_name;
For a more general solution, you can compare two lags:
select distinct cust_name
from (select c.*
lag(cust_id, 2) over (order by cust_id) as prev2_cust_id,
lag(cust_id, 2) over (partitioin by name order by cust_id) as prev2_cust_id_name
from customer c
) a c
where prev2_cust_id = prev2_cust_id_name;
This looks two rows back -- once only by cust_id and once only for the name. If the cust_id values are the same, then all rows have the same name. You can adjust 2 to any value.