Oracle Analytical Function?

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.

3 People found this is helpful