Add lastprice to all the rows with higher idexchangeplatform when idrequest and idpair are same [closed]

I have a table called ‘price’ with the following rows

idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40
    2         1               1               80
    2         1               2               250
    2         1               3               10
    3         1               1               110
    3         1               2               300
    3         1               3               60
    1         2               2               800
    1         2               3               1300
    2         2               2               1110
    2         2               3               1950

i need to make a SELECT QUERY WHERE idRequest and exchangePair match with the same table values but different idExchangePlatform

For example for this set of values:

idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40

i need to get the following output:

idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140

i’m using this query but it duplicates the result because is matching the idExchangePlatform in twisted order.

select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2
ON p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePatform;

This is the output i get from this query:

idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140
    1         1         2       1           195
    1         1         3       1           135
    1         1         3       2           140

Is there a way to prevent this with some CLAUSE?

Answer

Schema:

     create table price(idRequest int,  idPair int, idExchangePlatform int,  lastPrice int);
     insert into price values(1,         1,               1,               95);
     insert into price values(1,         1,               2,               100);
     insert into price values(1,         1,               3,               40);
     insert into price values(2,         1,               1,               80);
     insert into price values(2,         1,               2,               250);
     insert into price values(2,         1,               3,               10);
     insert into price values(3,         1,               1,               110);
     insert into price values(3,         1,               2,               300);
     insert into price values(3,         1,               3,               60);
     insert into price values(1,         2,               2,               800);
     insert into price values(1,         2,               3,               1300);
     insert into price values(2,         2,               2,               1110);
     insert into price values(2,         2,               3,               1950);

Query:

 select  p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b,  (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
 from price p1
 inner join price p2
 ON p1.idRequest = p2.idRequest and p1.idRequest=p1.idPair
 AND p1.idPair = p2.idPair AND p1.idExchangePlatform < p2.idExchangePlatform;

Output:

idRequest idPair idEP_a idEP_b lastPrice_sum
1 1 1 2 195
1 1 2 3 140
1 1 1 3 135
2 2 2 3 3060

db<>fiddle here