MySQL Query - recent entries per group -

i'm trying select recent entries per group in table.

say have table "blog_posts" has column "id" (all unique, auto incremented), "post_cat" can values 'category1' or 'category2' or 'category3', , "publish_status" column can values 'online' or 'offline'.

how can select recent entries each category?

i have following right now, feels it's selecting randomly:

select * `blog_posts` (publish_status = 'online') group post_cat order id desc limit 10 

i'd keep real simple , use trigger maintain last_post_id in category table can join on posts table - this:

simple query

select  pc.cat_id,  pc.name,  u.username,  bp.*  post_category pc inner join blog_post bp on pc.last_post_id = bp.post_id inner join users u on bp.user_id = u.user_id order  pc.cat_id;  +--------+------+----------+---------+---------+---------------------+ | cat_id | name | username | post_id | user_id | post_date           | +--------+------+----------+---------+---------+---------------------+ |      1 | cat1 | bar      |       3 |       2 | 2011-02-09 12:45:33 | |      2 | cat2 | bar      |       5 |       3 | 2011-02-09 12:45:33 | |      3 | cat3 | f00      |       4 |       1 | 2011-02-09 12:45:33 | +--------+------+----------+---------+---------+---------------------+ 

tables

drop table if exists post_category; create table post_category ( cat_id smallint unsigned not null auto_increment primary key, name varchar(255) unique not null, last_post_id int unsigned null, key (last_post_id) ) engine=innodb;  drop table if exists users; create table users ( user_id int unsigned not null auto_increment primary key, username varbinary(32) unique not null ) engine=innodb;  drop table if exists blog_post; create table blog_post ( post_id int unsigned not null auto_increment primary key, user_id int unsigned not null, post_date datetime not null, key (post_date, user_id) ) engine=innodb;  drop table if exists blog_post_category; create table blog_post_category ( cat_id smallint unsigned not null, post_id int unsigned not null, primary key (cat_id, post_id) ) engine=innodb; 

triggers

delimiter #  create trigger blog_post_before_ins_trig before insert on blog_post each row begin   set new.post_date = now(); end#  create trigger blog_post_category_before_ins_trig before insert on blog_post_category each row begin   update post_category set last_post_id = new.post_id cat_id = new.cat_id; end#  delimiter ; 

test data

insert post_category (name) values ('cat1'),('cat2'),('cat3'),('cat4'); insert users (username) values ('f00'),('bar'),('bar'),('alpha'),('beta');  insert blog_post (user_id) values (1),(1),(2),(1),(3); insert blog_post_category (cat_id, post_id) values (1,1),(1,3), (2,1),(2,5), (3,1),(3,3),(3,4); 

hope helps :)