I have a table where I keep products [prod]
and where they are sold and bought [market]
along with buying and selling prices [buy / sell]
. I want to get the minimum buying price and maximum selling price for each product along with market names. How can I get it done in MS SQL?
My table looks like this ;
id | prod |market| buy | sell|
--- ------ ------ ----- -----
1 | a | x | 25 | 26 |
2 | b | x | 15 | 16 |
3 | c | x | 17 | 19 |
4 | a | y | 24 | 25 |
5 | b | y | 14 | 17 |
6 | c | y | 19 | 24 |
7 | a | z | 23 | 24 |
8 | b | z | 17 | 18 |
9 | c | z | 18 | 22 |
And this is how the table I desired looks like;
|prod|MarketMin|MarketMax| Min Buy |Max Sell |
---- --------- --------- ---------- ---------
| a | z | x | 23 | 26 |
| b | y | z | 14 | 24 |
| c | x | y | 17 | 24 |
CodePudding user response:
You may use RANK()
in sub-query to achieve this:
SELECT mn.prod, mn.MarketMin, sq.MarketMax, mn.MinBuy, sq.MaxSell
FROM
(
SELECT prod, market AS MarketMin, buy AS MinBuy, RANK() OVER(PARTITION BY prod ORDER BY buy ASC) as buy_rnk
from MARKETVALUES
) as mn
INNER JOIN
(
SELECT prod, MarketMax, MaxSell
FROM
(
SELECT prod, market AS MarketMax, sell AS MaxSell, RANK() OVER(PARTITION BY prod ORDER BY sell DESC) as sell_rnk
from MARKETVALUES
) as mx
WHERE sell_rnk =1
) as sq
ON mn.prod=sq.prod
WHERE buy_rnk=1
Look at the demo in db<>fiddle