mysql按添加先后排序,在mysql之前按组排序数据（排序）

I want to group below data from sub_prd_id. but before that i need order data from max created_at.

I wrote query as below.

select * FROM prd_data group by sub_prd_id order by created_at desc

But this query not returned what i need. for example, according to below data, after query executes, result should be as below,

id name sub_prd_id created_at

4 Grape 10 2013-04-28 03:11:55

6 Banana 11 2013-04-28 03:23:14

7 Pineapple 12 2013-04-28 03:23:44

Here is Table Structure with data.

id name sub_prd_id created_at

2 Apple 10 2013-04-28 03:04:51

3 Orange 10 2013-04-28 03:08:19

4 Grape 10 2013-04-28 03:11:55

5 Mango 11 2013-04-28 03:22:48

6 Banana 11 2013-04-28 03:23:14

7 Pineapple 12 2013-04-28 03:23:44

解决方案

What you're trying to accomplish is known as a groupwise maximum, which can't be achieved using ORDER BY. Instead, one must find the MAX() and then join the result back to the table:

SELECT prd_data.* FROM prd_data NATURAL JOIN (

SELECT sub_prd_id, MAX(created_at) created_at

FROM prd_data

GROUP BY sub_prd_id

) t

See it on sqlfiddle.