MySQL: SELECT value and a minimum value on certain conditions in the same row

Question

(MySQL) In 'simple' terms I need to add a minimum price column. That is, the minimum price for each unique combination of PA and DA records.

Example Raw Data

id | PA  |     DA     |  price |
---|-----|------------|--------|
1  | SW1 |    PO19    |   100  | 
1  | W6  |    E16     |   5    |
2  | SW1 |    PO19    |   90   |
2  | W6  |    E16     |   8    |
3  | TW6 |    SO14    |   2000 |
3  | W6  |    E16     |   9    |

Output from Example

id | PA  |     DA     |  price | MIN price|
---|-----|------------|--------|--------- |
1  | SW1 |    PO19    |   100  |    90    |
1  | W6  |    E16     |   5    |    5     |
2  | SW1 |    PO19    |   90   |    90    |
2  | W6  |    E16     |   8    |    5     |
3  | TW6 |    SO14    |   2000 |    2000  |
3  | W6  |    E16     |   9    |    5     |

e.g. above: for PA=SW1, DA=PO19 the MIN price=90 (id=2).

Ideally I would also like to only SELECT a particular id, but it still returns the "global" minimum. e.g. if I want to select id=2, it returns:

id | PA  |     DA     |  price | MIN price|
---|-----|------------|--------|--------- |
2  | SW1 |    PO19    |   90   |    90    |
2  | W6  |    E16     |   8    |    5     |

I would post some attempts I've made but they've been useless attempts.

Regards,

George

Answer 1

The sub-select with the minimum price can be joined to the original table to get your result.

SELECT p.id, p.pa, p.da, p.price, minp.price min_price
FROM prices p
JOIN (SELECT pa, da, min(price) price from prices group by pa, da) minp
ON minp.pa = p.pa and minp.da = p.da
WHERE p.id = 2

Answer 2

You can use the the subquery like this check output here sqlFiddle

    select p.id,p.pa,p.da, 
(select min(s.price)
 from sample s 
group by s.pa,s.da 
having s.pa=p.pa and s.da=p.da)
 from sample p

mind the formatting