如何用order by 查询 栏目下最贵的商品

[molaifeng]

首先想到的用max来做 ：

select goods_id,goods_name,cat_id,shop_price from goods where shop_price in (select max(shop_price) from goods group by cat_id);   

后来试着用order by 来做
[code=HTML]
select shop_price from goods order by shop_price desc;
+------------+
| shop_price |
+------------+
| 5999.00 |
| 3700.00 |
| 3010.00 |
| 2878.00 |
| 2625.00 |
| 2300.00 |
| 2298.00 |
| 2000.00 |
| 2000.00 |
| 1388.00 |
| 1337.00 |
| 1328.00 |
| 1311.00 |
| 1300.00 |
| 983.00 |
| 858.00 |
| 823.33 |
| 788.00 |
| 399.00 |
| 280.00 |
| 100.00 |
| 95.00 |
| 90.00 |
| 68.00 |
| 58.00 |
| 48.00 |
| 45.00 |
| 42.00 |
| 20.00 |
| 19.00 |
| 18.00 |
+------------+
select shop_price from goods group by cat_id order by shop_price desc;
+------------+
| shop_price |
+------------+
| 3700.00 |
| 1388.00 |
| 823.33 |
| 399.00 |
| 95.00 |
| 90.00 |
| 68.00 |
| 48.00 |
| 20.00 |
+------------+

[code=HTML]
发现出现的数据并不是最大的排在的前面，之后自以为是的加了个limit 1；结果只出现了一行数据
请问如何在此基础上得到每个栏目最大的数：
[code=HTML]select shop_price from goods group by cat_id order by shop_price desc;[code=HTML]

[molaifeng]

诶，怎么就没人回答呢，还好刚刚在网上仔细看了看有关mysql的教程，发现from子查询，小试牛刀，嚯，居然成功了。

select * from (select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc) as tmp group by cat_id;

[rucypli]

呵呵 自学的

[molaifeng]

呵呵，终于有人了，可以结贴了！！！