Hello. Anybody know how to combine these into one query? This is mysql 4.1, so no subselects for me :(

I want to get the latest blog entry from every blog_id. each entry is stored in this one table.

$q = SQL::Query("CREATE TEMPORARY TABLE blogsort SELECT blog_entry_id, blog_id, blog_subject, timestamp, handle, name_first, name_last, LEFT(blog_text, 100) as excerpt FROM blog_entries as b

				LEFT JOIN lu__users as u ON (b.blog_id = u.auth_user_id)
				ORDER BY timestamp DESC
				");

$q = SQL::Query("SELECT * FROM blogsort

				GROUP BY blog_id
				ORDER BY timestamp DESC
				");

SELECT * FROM blogsort
GROUP BY blog_id
ORDER BY timestamp DESC

Does this work ? If you are grouping by blog_id then all other columns should somehow be aggregated, no ?

You'd think so, but what happens is the GROUP occurs first, so it picks an essencially random crieteria as the 'first row of the group'. Next, it orders the results by timestamp. I usually end up with the second blog entry of each blog id using that statement.

>>3
MySQL is kind of gay, if you ask me...

Anyway, I can't think of any way to combine those two queries without subselects. But her is another version:

SELECT blog_id, MAX(timestamp) AS timestamp INTO #blogsort FROM blog_entries GROUP BY blog_id

SELECT be.blog_id, be.blog_entry_id, be.blog_subject, be.timestamp, LEFT(be.blog_text, 100) AS excerpt, u.handle, u.name_first, u.name_last FROM blog_entries AS be
INNER JOIN lu__users AS u ON u.auth_user_id = be.blog_id
INNER JOIN #blogsort AS bs ON bs.blog_id = be.blog_id AND bs.timestamp = be.timestamp
ORDER BY be.timestamp DESC

Not pretty but should be faster and almost as good as your approach. You decide.

Thabks for your help anonymouses.

How about you do the join in the client?

http://jan.kneschke.de/projects/mysql/groupwise-max/ has examples of finding the groupwise maximum without subqueries or multiple queries.

>>7

SELECT co1.continent, co1.name FROM Country AS co1
JOIN Country AS co2 ON co1.continent = co2.continent
GROUP BY co1.continent, co1.name
HAVING co1.population = MAX(co2.population)

Oh, this is very nice and simple, stupid me for not thinking of that. Thanks for sharing.