0

How can I find the maximum with multiple columns in MySQL?

I can only find the MAX Value when I have two columns. As soon as I put more than two columns in GROUP BY, it show me all values again. 

SELECT d_name,description,fabric,colour,pattern,size_q,MAX(price) as pr 
FROM
 (
SELECT d_name,description,fabric,colour,pattern,size_q,ROUND(SUM(labour_cost+(cost*quantity)),1) as price

 FROM dressmaker d 
 
 join dress_order dr
 JOIN construction c
 JOIN order_line o
 JOIN material m 
 join garment g
 join quantities q
ON d_no=c.maker 
AND o.order_ref=c.order_ref 
AND c.line_ref=o.line_no 
AND o.ol_material=m.material_no
AND o.ol_style=g.style_no
AND o.ol_size=q.size_q
AND o.ol_style=q.style_q
AND dr.order_no=o.order_ref
AND completed='Y'

GROUP BY d_name,description,fabric,colour,pattern,size_q) as p

GROUP BY d_name,description,fabric,colour,pattern,size_q

but I get all values in MAX(price)

d_name | description | fabric | color | pattern | size_q| MAX(price) |

-------+-------------+--------+-------+---------+-------+-------------

Mrs Hem| Trousers | Silk | Black | Plain | 8 | 36.9. |

Mrs Hem| Long Skirt| Silk| 'Green | 'Printed'| '14'| '72.0'|

'Miss Stitch'| 'Long Skirt'| 'Silk'| 'Red Abstract'| 'Printed'| '8'| '49.0'|

'Miss Stitch'| 'Shorts'| 'Cotton'| 'Pink Check'| 'Woven'| '16'| '14.8'|

I need only the MAX Values in MAX(Price)

|d_name | description | fabric | color | pattern | size_q| MAX(price) |

|Mrs Hem| Long Skirt| Silk| 'Green | 'Printed'| '14'| '72.0'|

'Miss Stitch'| 'Long Skirt'| 'Silk'| 'Red Abstract'| 'Printed'| '8'| '49.0'|

bubi
  • 11
  • 2

1 Answers1

-1

Add ORDER BY MAX(price) DESC LIMIT 1 to the end of your query.

Neel
  • 597
  • 6
  • 19
  • sorry i edited it again. It should show me all MAX values from d_name's and also have description, fabric,color,pattern size_q,MAX(price) – bubi May 10 '20 at 09:18
  • Which columns do you want to get the maximum with respect to? Do you want the maximum for each `d_name`? If so, you'll need to only `SELECT d_name, MAX(price) AS max_price` in the outer query. To get the rest of the columns back, you can join with the original table and filter where `price = max_price`. – Neel May 10 '20 at 09:25