[RESOLVED] Sorting an order by

[Pig]

I am querying a moveable type database to get the most recent entries for each blog. I am doing a GROUP BY on the blog_id to get only one blog, and MAX() on the create date in order to get the most recent. When I did this, I found two problems.

1. The query did not respect my ORDER BY. The manual said this is expected behavior when using GROUP BY. I solved this by wrapping the query in a subselect in order to apply ordering after-the-fact.

2. The GROUP BY is not grouping items from the same row. It gets the MAX date, but not the corresponding id,title,etc for the row with the MAX date.

For example:

+----------+-------------+------------------+---------------+
| entry_id | entry_title | entry_created_on | entry_blog_id | 
+----------+-------------+------------------+---------------+
| 1        | foo         | 2007-05-01       | 1             | 
+----------+-------------+------------------+---------------+
| 2        | bar         | 2007-04-01       | 1             | 
+----------+-------------+------------------+---------------+
| 3        | baz         | 2007-03-01       | 1             | 
+----------+-------------+------------------+---------------+

I would expect to get back:

+----------+-------------+------------------+---------------+
| entry_id | entry_title | entry_created_on | entry_blog_id | 
+----------+-------------+------------------+---------------+
| 1        | foo         | 2007-05-01       | 1             | 
+----------+-------------+------------------+---------------+

But instead I get back something like:

+----------+-------------+------------------+---------------+
| entry_id | entry_title | entry_created_on | entry_blog_id | 
+----------+-------------+------------------+---------------+
| 3        | baz         | 2007-05-01       | 1             | 
+----------+-------------+------------------+---------------+

Is there a better way to do this? Here is my query:

	SELECT 
		* 
	FROM 
		(
			SELECT 
				MAX(entry_created_on) as date,
				blog_name,
				entry_title as title,
				entry_id
			FROM 
				mt_entry
			INNER JOIN mt_blog ON 
				mt_blog.blog_id = mt_entry.entry_blog_id
			WHERE 
				entry_status = 2
			GROUP BY 
				blog_id
			ORDER BY 
				null
		) AS foo 
	ORDER BY 
		date DESC

Thank you!

[tomhath]

This was discussed in a thread a couple of weeks ago, maybe search for it (I'm too lazy). Basically MySQL lets you group on some but not all of the fields selected, but they had a bug that makes it unreliable in certain releases.

I would write it something like below (not tested). This is standard SQL instead of using the MySQL feature. Use a subselect to find the entry on the most recent date, then join back to that entry.

	SELECT 
		mt_entry.*, mt_blog.*
	FROM 
		(
			SELECT 
				entry_blog_id, MAX(entry_created_on) as date 
			FROM 
				mt_entry
			WHERE 
				mt_entry.entry_status = 2
			GROUP BY 
				entry_blog_id
		) as mte
	INNER JOIN mt_entry ON
			mt_entry.blog_id = mte.entry_blog_id
		AND mt_entry.entry_created_on = mte.date
	INNER JOIN mt_blog ON 
			mt_blog.blog_id = mte.entry_blog_id
	WHERE 
			mt_entry.entry_status = 2
	ORDER BY 
		mte.date DESC

[Piranha]

Another way (untested) to do the same thing:

	SELECT 
		* 
	FROM 
		(
			SELECT 
				entry_created_on as date,
				blog_name,
				entry_title as title,
				entry_id
			FROM 
				mt_entry
			INNER JOIN mt_blog ON 
				mt_blog.blog_id = mt_entry.entry_blog_id
			WHERE 
				entry_status = 2
			ORDER BY 
				entry_created_on DESC
		) AS foo 
	GROUP BY 
		entry_blog_id DESC

[Pig]

Awsome, thanks! I tried tomhath's suggestion, and it appears to be working.

[Kudose]

If this thread is resolved, please mark it resolved using the thread tools.