mysql - select the 2nd blog from each category -

how can 2nd post each category in php mysql?

if have table following:

post_id  |   cate_id --------------------     1     |      2    2     |      4    3     |      2    4     |      1    5     |      1    6     |      3    7     |      4    8     |      3    9     |      5    10    |      5 

expecting result following:

post_id  |   cate_id  --------------------      5    |     1     3    |     2     8    |     3     7    |     4     10   |     5 

thanks.

select t.cate_id,min(t.post_id) (select cate_id, min(post_id) post_id foo group 1) firsts inner join foo t on t.cate_id=firsts.cate_id , t.post_id > firsts.post_id group 1; 

given:

mysql> select cate_id, group_concat(post_id order post_id) foo group 1; +---------+----------------------------------------+ | cate_id | group_concat(post_id order post_id) | +---------+----------------------------------------+ |       0 | 0,1,3,4,7                              | |       1 | 0,1,2,4,6,8,9                          | |       3 | 7,11                                   | |       4 | 2,3                                    | |       5 | 1,3,9                                  | |       6 | 0,2,5,6,7,8,9                          | |       7 | 0,2                                    | |       8 | 0,1,4,6,7,8,9                          | |       9 | 1,2                                    | +---------+----------------------------------------+ 

produces:

mysql> select t.cate_id,min(t.post_id) (select cate_id, min(post_id) post_id foo group 1) firsts inner join foo t on t.cate_id=firsts.cate_id , t.post_id > firsts.post_id group 1; +---------+----------------+ | cate_id | min(t.post_id) | +---------+----------------+ |       0 |              1 | |       1 |              1 | |       3 |             11 | |       4 |              3 | |       5 |              3 | |       6 |              2 | |       7 |              2 | |       8 |              1 | |       9 |              2 | +---------+----------------+