id item price
1 20 10000
2 20 145600
3 20 1054400
4 21 10400
5 21 15000
6 20 10600
7 21 101200
8 21 1456001我想按照id降序取出item的最新价格
也就是说结果是
8 21 1456001
6 20 10600请各位大侠帮忙
1 20 10000
2 20 145600
3 20 1054400
4 21 10400
5 21 15000
6 20 10600
7 21 101200
8 21 1456001我想按照id降序取出item的最新价格
也就是说结果是
8 21 1456001
6 20 10600请各位大侠帮忙
[征集]分组取最大N条记录方法征集,及散分....
from (select * from table1 order by item,id desc) t
group by item
+------+------+---------+
| id | item | price |
+------+------+---------+
| 1 | 20 | 10000 |
| 2 | 20 | 145600 |
| 3 | 20 | 1054400 |
| 4 | 21 | 10400 |
| 5 | 21 | 15000 |
| 6 | 20 | 10600 |
| 7 | 21 | 101200 |
| 8 | 21 | 1456001 |
+------+------+---------+
8 rows in set (0.00 sec)mysql> select *from test a where not exists
(select 1 from test where item =a.item and id>a.id) order by id desc;
+------+------+---------+
| id | item | price |
+------+------+---------+
| 8 | 21 | 1456001 |
| 6 | 20 | 10600 |
+------+------+---------+
2 rows in set (0.00 sec)