How can I get minimum and maximum values from different column1 and column2 group by column3?

Question

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    |

Answer 1

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

Edit: Demo_v2

Demo_v2 has alternate query (without using RANK() but using multiple JOINS and sub-queries). This method will be slower than RANK().