Ordered and Grouped Select

ucbones

Hi,

I have the following SQL code:

SELECT * FROM module_blog_entries
LEFT JOIN module_blog_blogs ON module_blog_entries.blogID=module_blog_blogs.ID
GROUP BY module_blog_entries.blogID
ORDER BY module_blog_entries.tstamp DESC
LIMIT 20

Unfortunately, it appears to be grouping before ordering. In other words, it's bundling all the entries in the same blog together before sorting them, so I'm getting returned the oldest (and hence lowest tstamped) entries from each blog, ordered by the entries in the blog with the newest first entry.

I would do a bit of PHP trickery, but then I wouldn't necessarily get 20 entries.

This can't be so hard!!!

Any ideas?

ucbones

Roger_Ramjet

Started to answer this then realised I'm not sure what you are actually trying to return, with your limit 20 and all.

Is it the newest 20 blog entries regradless of blog: or the last 20 blogs to have an entry?

ucbones

I want to return the last 20 entries of all entries written, but only include one entry from each blog (i.e. if someone's crazy keen and writes 20 entries in quick succession, it doesn't matter, because we're only including one from each blog).

I could do it with some loop trickery in PHP, but that just doesn't seem to be very elegant!

Cheers,

ucbones

Roger_Ramjet

OK, then you want the MAX(tstamp) for each BlogID, ordered by tstamp DESC and limit it to 20.

My pref would be with subquery: habit from Access and the way Jet works.

Keeping it simple

// basic query to return the ids of the 20 blogs with the most recent entry
$sql = "SELECT blogid as id, MAX(tstamp) 
FROM module_blog_entries
GROUP BY blogid, tstamp
ORDER BY tstamp DESC
LIMIT 20";

// I would use it as a subquery to select blog fields
$sql = "SELECT * FROM module_blog_blogs WHERE id IN (SELECT blogid as id, MAX(tstamp) as tstamp
FROM module_blog_entries
GROUP BY blogid, tstamp
ORDER BY tstamp DESC
LIMIT 20)";

// you can use as many joins etc as you want in the outer query, depends on your db and version if it supports subqueries