Get record with Max value from SELECT query

Question

I am trying to get max value of a column in my query but I cant found any solution. My query is

SELECT pz.id, 
    sz.Price + cr.AddedCost AS price
FROM piz pz, crust cr, size sz
WHERE 
    pz.SizeID = sz.id AND
    pz.CrustID = tp.id

Then I receive some records like the following

ID  price
-----------
1   95000.0
6   160000.0
10  150000.0
3   137500.0
5   122500.0
4   195000.0
2   195000.0
7   130000.0
8   205000.0
9   130000.0
11  205000.0

I want to get the record with the max value of price, which are:

ID  price
------------
8   205000.0
11  205000.0

I am using Navicat with MySQL server

Update: I did try using:

SELECT pz.id, MAX(sz.Price + cr.AddedCost) AS price ....

But it didnt work as the returned id is wrong

Answer 1

What happens when you do this.

Change all your old style comma separated join to Inner join for better readability.

SELECT pz.id,
       sz.Price + cr.AddedCost AS price
FROM   piz pz
       INNER JOIN crust cr
               ON pz.CrustID = cr.id
       INNER JOIN size sz
               ON pz.SizeID = sz.id
WHERE  sz.Price + cr.AddedCost = (SELECT sz.Price + cr.AddedCost AS price
                                   FROM   piz pz1
                                          INNER JOIN crust cr1
                                                  ON pz1.CrustID = cr1.id
                                          INNER JOIN size sz1
                                                  ON pz1.SizeID = sz1.id
                                   ORDER  BY price DESC limit 1 )