Deceptive MySQL Query

Question

So I don't consider myself a novice at MySQL but this one has me stumped:

I have a message board and I want to pull a list of all the most recent posts grouped by the Thread ID.

Here's the table:

MB_Posts
-ID
-Thread_ID
-Created_On (timestamp)
-Creator_User (user_id)
-Subject
-Contents
-Edited (timestamp)
-Reported

I've tried many different things to keep it simple but I would like help from the community on this one.

Just to kick this out there...this one does not work as expected:

SELECT * FROM MB_Posts GROUP BY Thread_ID ORDER BY ID DESC

Desired results:

A set of the most recent posts, one per thread ID

Answer 1

When you use GROUP BY, you have to have an aggregate function in the SELECT part of the statement.

Answer 2

Ok, I have a suggestion. Let me know:

SELECT 
    MB_Posts.*, 
    (SELECT 
        GROUP_CONCAT(CreatedOn ORDER BY CreatedOn DESC) 
        FROM MB_Posts AS P 
        WHERE P.Thread_ID = MB_Posts.Thread_ID) 
    AS `sort`  
FROM MB_Posts 
ORDER BY `sort` DESC, CreatedOn DESC 

Answer 3

Here is another way:

SELECT * FROM mb_posts ORDER BY ID DESC

threadsHistory = array();
while (row)
{
    if (row) NOT IN threadsHistory
    {
         add values to array (for example thread_ID and postID) 
         count++;
    }
    if count == 10 break;
}

this is not exactly a nice and clean way, but it should work with a little twisting

Answer 4

GROUP BY expects to return one row per unique group by, e.g.

SELECT COUNT(*) FROM mb_post GROUP BY thread_id;

Will return the number of posts in each thread.

You will need to do an order by thread_id, id and then programmatically handle the "grouping" by that you want, e.g.

for (row in rows) {
    print("<tr><th>");
    if (row["thread_id"] != prev_id) {
      print(thread_id)
      prev_id = thread_id;
    }
    print("</th>")

Answer 5

Give this a whirl and see if it fits what you want. This should show you the most recent 100 posts and the results will come back sorted by the thread that they belong to.

SELECT MB_Posts.* 
  FROM MB_Posts 
  INNER JOIN (
    SELECT ID FROM MB_Posts ORDER BY Created_On DESC LIMIT 0, 100
  ) as MostRecentPosts ON MB_Posts.ID = MostRecentPosts.ID
  ORDER BY Thread_ID, Created_On DESC;

Based on your revision "A set of the most recent posts, one per thread ID", the table created from the subquery can be created using a little concat and compare magic. This will depend on what your column types are but should work for most. The idea of both of these solutions is to use a subquery to handle your aggregation process and determine what the ID's are of the desired records, and then to join the table against the results of that subquery to retrieve the rest of the data for the row.

SELECT MB_Posts.* 
  FROM MB_Posts 
  INNER JOIN (
    SELECT TRIM(LEADING '0' FROM
      SUBSTRING(MAX(CONCAT(UNIX_TIMESTAMP(Created_On), LPAD(ID,15,'0'))), -15)) AS ID
    FROM MB_Posts 
    GROUP BY Thread_ID
  ) as MostRecentPostPerThread ON MB_Posts.ID = MostRecentPostPerThread.ID
  ORDER BY Thread_ID, Created_On DESC;

Answer 6

GROUP BY overrides ORDER BY (with no exceptions).

SELECT * 
FROM MB_Posts 
WHERE ID IN (SELECT MAX(ID) FROM MB_Posts GROUP BY Thread_ID)

should give you what you need.

For details read about hidden columns in group by (in your original query all your columns except Thread_ID are hidden)

Answer 7

Hmm... Does Created_On or Edited determine "most recent?"

Presuming Created_On is what's wanted:

SELECT Thread_ID, MAX(Created_On) FROM MB_Posts GROUP BY Thread_ID ;

gives the latest timestamp per Thread_ID. Semijoin these to MB_Posts with something like

SELECT * FROM MB_Posts JOIN ( SELECT Thread_ID, MAX(Created_On) AS Created_On FROM MB_Posts GROUP BY Thread_ID ) LATEST USING (Thread_ID, Created_On) ;