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 |