MySQL 8.0

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

create table test (customerID INT, orderID INT, amount INT);
INSERT INTO test VALUES
(1,1,1),
(1,2,7),
(1,3,2),
(1,1,5),
(1,2,8),
(1,3,4),
(1,1,3),
(1,2,6);
SELECT * FROM test;


customerID
orderID
amount


1
1
1

1
2
7

1
3
2

1
1
5

1
2
8

1
3
4

1
1
3

1
2
6


SELECT *, LEAD(amount) OVER (PARTITION BY customerID ORDER BY orderID) next_amount
FROM test
ORDER BY customerID, orderID, amount;


customerID
orderID
amount
next_amount


1
1
1
5

1
1
3
7

1
1
5
3

1
2
6
2

1
2
7
8

1
2
8
6

1
3
2
4

1
3
4
null


ALTER TABLE test ADD UNIQUE (amount, orderID, customerID);
SELECT *, LEAD(amount) OVER (PARTITION BY customerID ORDER BY orderID) next_amount
FROM test
ORDER BY customerID, orderID, amount;


customerID
orderID
amount
next_amount


1
1
1
3

1
1
3
5

1
1
5
6

1
2
6
7

1
2
7
8

1
2
8
2

1
3
2
4

1
3
4
null