(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
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