By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
create table t1
(Bank1 varchar(10),
TotalLine varchar(10),
Zones varchar(10),
Binder varchar(10),
value varchar(10)
);
insert into t1
VALUES('B1','TL1','Y','BIND1',1),('B1','TL1','Y','BIND1',2),('B1','TL1','Y','BIND1',1),('B1','TL1','N','BIND1',11),
('B1','TL1','N','BIND1',15),('B1','TL1','Y','BIND1',17),('B1','TL1','Y','BIND1',56),('B1','TL1','Y','BIND1',2),('B1','TL1','Y','BIND1',3),('B2','TL2','Y','BIND2',5),('B2','TL2','Y','BIND2',3),('B2','TL2','Y','BIND2',2),('B2','TL2','Y','BIND2',11),('B2','TL2','Y','BIND2',7),('B2','TL2','Y','BIND2',9),('B2','TL2','Y','BIND2',11),('B2','TL2','N','BIND2',62),('B2','TL2','N','BIND2',32)
Records: 18 Duplicates: 0 Warnings: 0
select * from t1
| Bank1 | TotalLine | Zones | Binder | value |
|---|---|---|---|---|
| B1 | TL1 | Y | BIND1 | 1 |
| B1 | TL1 | Y | BIND1 | 2 |
| B1 | TL1 | Y | BIND1 | 1 |
| B1 | TL1 | N | BIND1 | 11 |
| B1 | TL1 | N | BIND1 | 15 |
| B1 | TL1 | Y | BIND1 | 17 |
| B1 | TL1 | Y | BIND1 | 56 |
| B1 | TL1 | Y | BIND1 | 2 |
| B1 | TL1 | Y | BIND1 | 3 |
| B2 | TL2 | Y | BIND2 | 5 |
| B2 | TL2 | Y | BIND2 | 3 |
| B2 | TL2 | Y | BIND2 | 2 |
| B2 | TL2 | Y | BIND2 | 11 |
| B2 | TL2 | Y | BIND2 | 7 |
| B2 | TL2 | Y | BIND2 | 9 |
| B2 | TL2 | Y | BIND2 | 11 |
| B2 | TL2 | N | BIND2 | 62 |
| B2 | TL2 | N | BIND2 | 32 |
select *,max(value) over (partition by Binder) as highest from t1
| Bank1 | TotalLine | Zones | Binder | value | highest |
|---|---|---|---|---|---|
| B1 | TL1 | Y | BIND1 | 2 | 56 |
| B1 | TL1 | Y | BIND1 | 1 | 56 |
| B1 | TL1 | N | BIND1 | 11 | 56 |
| B1 | TL1 | N | BIND1 | 15 | 56 |
| B1 | TL1 | Y | BIND1 | 17 | 56 |
| B1 | TL1 | Y | BIND1 | 56 | 56 |
| B1 | TL1 | Y | BIND1 | 2 | 56 |
| B1 | TL1 | Y | BIND1 | 3 | 56 |
| B1 | TL1 | Y | BIND1 | 1 | 56 |
| B2 | TL2 | N | BIND2 | 32 | 9 |
| B2 | TL2 | N | BIND2 | 62 | 9 |
| B2 | TL2 | Y | BIND2 | 11 | 9 |
| B2 | TL2 | Y | BIND2 | 9 | 9 |
| B2 | TL2 | Y | BIND2 | 7 | 9 |
| B2 | TL2 | Y | BIND2 | 11 | 9 |
| B2 | TL2 | Y | BIND2 | 2 | 9 |
| B2 | TL2 | Y | BIND2 | 3 | 9 |
| B2 | TL2 | Y | BIND2 | 5 | 9 |
select t1.*,
min(case when zones = 'N' then value end) over (partition by binder) as lowest,
max(case when zones = 'N' then value end) over (partition by binder) as highest
from t1
| Bank1 | TotalLine | Zones | Binder | value | lowest | highest |
|---|---|---|---|---|---|---|
| B1 | TL1 | Y | BIND1 | 2 | 11 | 15 |
| B1 | TL1 | Y | BIND1 | 1 | 11 | 15 |
| B1 | TL1 | N | BIND1 | 11 | 11 | 15 |
| B1 | TL1 | N | BIND1 | 15 | 11 | 15 |
| B1 | TL1 | Y | BIND1 | 17 | 11 | 15 |
| B1 | TL1 | Y | BIND1 | 56 | 11 | 15 |
| B1 | TL1 | Y | BIND1 | 2 | 11 | 15 |
| B1 | TL1 | Y | BIND1 | 3 | 11 | 15 |
| B1 | TL1 | Y | BIND1 | 1 | 11 | 15 |
| B2 | TL2 | N | BIND2 | 32 | 32 | 62 |
| B2 | TL2 | N | BIND2 | 62 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 11 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 9 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 7 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 11 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 2 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 3 | 32 | 62 |
| B2 | TL2 | Y | BIND2 | 5 | 32 | 62 |