MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.29


CREATE TABLE sales (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
serial_no VARCHAR(30) NOT NULL,
card_amt int(6),
pos_no VARCHAR(50)
);

INSERT INTO sales(serial_no,card_amt, pos_no) 
VALUES('100','10','PS1'),('101','5','PS1'),('102','10','PS1'), 
('103','10','PS1'), ('107','10','PS1'),('108','10','PS1'), ('109','10','PS1'),
('111','10','PS1'),('200','10','PS2');
select * from sales;


id
serial_no
card_amt
pos_no


1
100
10
PS1

2
101
5
PS1

3
102
10
PS1

4
103
10
PS1

5
107
10
PS1

6
108
10
PS1

7
109
10
PS1

8
111
10
PS1

9
200
10
PS2


select dn.serial_no as dnval, min(up.serial_no) as upval, dn.card_amt, dn.pos_no  
from sales up
join sales dn
    on dn.serial_no <= up.serial_no
    and dn.pos_no = up.pos_no
    and dn.card_amt = up.card_amt
where not exists (
    select 1 from sales a 
    where a.serial_no = up.serial_no + 1 
    and a.pos_no = up.pos_no
    and a.card_amt = up.card_amt
)
  and not exists (
  select 1 from sales b 
  where b.serial_no = dn.serial_no - 1 
  and b.pos_no = dn.pos_no
  and b.card_amt = dn.card_amt
)
group by dn.pos_no, dn.serial_no, dn.card_amt
order by dn.pos_no, dn.serial_no;


dnval
upval
card_amt
pos_no


100
100
10
PS1

101
101
5
PS1

102
103
10
PS1

107
109
10
PS1

111
111
10
PS1

200
200
10
PS2

id	serial_no	card_amt	pos_no
1	100	10	PS1
2	101	5	PS1
3	102	10	PS1
4	103	10	PS1
5	107	10	PS1
6	108	10	PS1
7	109	10	PS1
8	111	10	PS1
9	200	10	PS2

dnval	upval	card_amt	pos_no
100	100	10	PS1
101	101	5	PS1
102	103	10	PS1
107	109	10	PS1
111	111	10	PS1
200	200	10	PS2

id	serial_no	card_amt	pos_no
1	100	10	PS1
2	101	5	PS1
3	102	10	PS1
4	103	10	PS1
5	107	10	PS1
6	108	10	PS1
7	109	10	PS1
8	111	10	PS1
9	200	10	PS2

dnval	upval	card_amt	pos_no
100	100	10	PS1
101	101	5	PS1
102	103	10	PS1
107	109	10	PS1
111	111	10	PS1
200	200	10	PS2

id	serial_no	card_amt	pos_no
1	100	10	PS1
2	101	5	PS1
3	102	10	PS1
4	103	10	PS1
5	107	10	PS1
6	108	10	PS1
7	109	10	PS1
8	111	10	PS1
9	200	10	PS2

dnval	upval	card_amt	pos_no
100	100	10	PS1
101	101	5	PS1
102	103	10	PS1
107	109	10	PS1
111	111	10	PS1
200	200	10	PS2