2

I'm trying to select the most recent entries per group in a table.

Say I have a table "blog_posts" which has a column for "id" (all unique, auto incremented), "post_cat" which can be values 'category1' or 'category2' or 'category3', and a "publish_status" column which can be values 'online' or 'offline'.

How can I select the most recent entries for each category?

I have the following right now, but it almost feels like it's selecting randomly:

select * FROM `blog_posts` WHERE (publish_status = 'online') GROUP BY post_cat ORDER BY id DESC LIMIT 10
blahdiblah
  • 33,069
  • 21
  • 98
  • 152
Jay
  • 1,084
  • 4
  • 18
  • 43
  • It kinda is selecting randomly, since the behavior of selecting columns that are not part of a `GROUP BY` clause when `GROUP BY` is used... is undefined. Per the MySQL manual. – Dan Grossman Feb 09 '11 at 11:53
  • Duplicate of: http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group – Dan Grossman Feb 09 '11 at 11:54
  • see http://explainextended.com/2010/03/18/greatest-n-per-group-dealing-with-aggregates/ – Haim Evgi Feb 09 '11 at 11:56

3 Answers3

1

I'd keep it real simple and use a trigger to maintain a last_post_id in the category table so you can easily join back on the posts table - something like this:

Simple Query

select
 pc.cat_id,
 pc.name,
 u.username,
 bp.*
from
 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 by
 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
for each row
begin
  set new.post_date = now();
end#

create trigger blog_post_category_before_ins_trig before insert on blog_post_category
for each row
begin
  update post_category set last_post_id = new.post_id where cat_id = new.cat_id;
end#

delimiter ;

Test Data

insert into post_category (name) values ('cat1'),('cat2'),('cat3'),('cat4');
insert into users (username) values ('f00'),('bar'),('BAR'),('alpha'),('beta');

insert into blog_post (user_id) values (1),(1),(2),(1),(3);
insert into blog_post_category (cat_id, post_id) values
(1,1),(1,3),
(2,1),(2,5),
(3,1),(3,3),(3,4);

Hope this helps :)

Jon Black
  • 16,223
  • 5
  • 43
  • 42
0

There's no really straightforward way to go about this...

http://www.artfulsoftware.com/infotree/queries.php#104

http://planet.mysql.com/entry/?id=26926

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • I tried this, but still returns the same result: select f.id, f.post_date, f.post_content, f.post_title, f.post_cat from ( select id, post_date, post_content, post_title, post_cat, max(id) as max_id from blog_posts group by post_cat ) as x inner join blog_posts as f on f.post_cat = x.post_cat and f.id = x.id; – Jay Feb 09 '11 at 12:42
  • `select f.id, f.post_date, f.post_content, f.post_title, f.post_cat from ( select id, post_date, post_content, post_title, post_cat, max(id) as max_id from blog_posts group by post_cat ) as x inner join blog_posts as f on f.post_cat = x.post_cat and f.id = x.id;` – Jay Feb 09 '11 at 12:48
0

If you only have three categories, you can just make separate queries and take the union:

(SELECT * FROM `blog_posts` WHERE `publish_status` = 'online' AND `post_cat`='category1' ORDER BY `id` DESC LIMIT 10) UNION
(SELECT * FROM `blog_posts` WHERE `publish_status` = 'online' AND `post_cat`='category2' ORDER BY `id` DESC LIMIT 10) UNION
(SELECT * FROM `blog_posts` WHERE `publish_status` = 'online' AND `post_cat`='category3' ORDER BY `id` DESC LIMIT 10)

You could even re-sort the whole thing at the end. It's only 30 rows!

SELECT * FROM (
    (SELECT * FROM `blog_posts` WHERE `publish_status` = 'online' AND `post_cat`='category1' ORDER BY `id` DESC LIMIT 10) UNION
    (SELECT * FROM `blog_posts` WHERE `publish_status` = 'online' AND `post_cat`='category2' ORDER BY `id` DESC LIMIT 10) UNION
    (SELECT * FROM `blog_posts` WHERE `publish_status` = 'online' AND `post_cat`='category3' ORDER BY `id` DESC LIMIT 10)
) `monster` ORDER BY `id` DESC
awm
  • 6,526
  • 25
  • 24