Get the best offer MySQL query

I need the highest/best offer from this table by shop!

offer    |    f_shop  | price
5253          100       10   
5254          101       12
5255          101       16
5256          102       25
5257          102       11
5258          100       36

What is the best way to get the highest offer by shop? Group by and max() don't give me the correct result.

---

OK, I'm sorry that my question wasn't precise enough.

Here is my actual query:

SELECT s.ch_f1 shop, a.ch_f3 price,a.bool_f11 currency,aa.priority AS priority FROM offers a LEFT JOIN shops s ON a.ch_f5=s.extern_id LEFT JOIN offertypes aa ON a.f_offertype=aa.id WHERE 
s.bool_f1=1 AND 
a.bool_f1=1 AND 
a.bool_f4=0
ORDER BY priority ASC, price DESC, currency

This is the result:

shop; price; currency; priority
Shop A  100.00  1   1
Shop B  50.00   1   1
Shop C  50.00   1   1
Shop D  50.00   1   1
Shop E  50.00   1   1
Shop B  50.00   1   1
Shop A  50.00   1   1
Shop F  40.00   1   1
Shop G  40.00   1   1
Shop H  40.00   1   1
Shop B  39.00   1   1
Shop B  35.00   1   1
Shop B  33.00   1   1
Shop A  30.00   2   1
Shop A  30.00   2   1
Shop X  30.00   2   1
Shop Y  30.00   2   1
Shop Z  30.00   2   1
Shop Q  30.00   2   1

But I want to have the best offer from all available shops. And I need the order value too开发者_开发技巧. I have no idea how I can do this.

---

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

---

If you want the highest offer for a given shop you can do this:

select max(offer) from yourtable where f_shop = 100;

---

may be you are looking for this

select * from table group by f_shop having max(price)

will give all the shops row with maximum price.