MySQL Dummy Request

Question

So, I don't consider myself a newbie to MySQL, but I have a dead end:

I have a message board, and I want to list all the latest posts grouped by thread ID.

Here is the table:

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

I tried many different things to make it simple, but I would like to help the community on this.

Just to kick this out ... this doesn't work as expected:

 SELECT * FROM MB_Posts GROUP BY Thread_ID ORDER BY ID DESC 

Desired Results:

A set of recent messages, one per thread ID

Answer 1

GROUP BY overrides ORDER BY (no exceptions).

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

should provide you with what you need.

More information about hidden columns in the group by (in the original query, all your columns except Thread_ID are hidden)

Answer 2

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

Answer 3

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 4

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 a good and clean way, but it should work with a little twisting

Answer 5

GROUP BY expects to return one row for each individual group, for example,

 SELECT COUNT(*) FROM mb_post GROUP BY thread_id; 

The number of messages in each thread will be returned.

You will need to order thread_id, id, and then programmatically process the “grouping” with what you want, for example.

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

Answer 6

Give it a whirlwind and see if it fits what you want. This should show you the last 100 posts, and the results will be returned sorted by the stream to which they belong.

 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, “Set the most recent posts, one per thread ID,” a table created from a subquery can be created using a small amount of concat and compare magic. This will depend on what your column types are, but they should work for most. The idea of ​​both of these solutions is to use a subquery to process your aggregation process and determine that the identifier has the required records, and then join the table against the results of this subquery to get 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 7

Hmm ... Does Created_On or Edited define "the most recent?"

It is assumed that created_On is needed:

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

gives the last timestamp for Thread_ID. Get it on 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) LAST USE (Thread_ID, Created_On);