SQL join where group by and order by, cant get it to show in the right order

Question

Im trying to join two tables, the table i am joining contains several rows of the same id's

I really cant get it to sort after "ThreadReplied" date...

I have tried severeal ways of this query:

$sql = "SELECT
ThreadHead.ThreadID,
ThreadHead.ThreadBy,
ThreadHead.ThreadStarted,
ThreadHead.ThreadSubject,
ThreadHead.ForumID
FROM ThreadHead
JOIN (SELECT ThreadReplied,ThreadID FROM ThreadPosts) AS TH
ON ThreadHead.ThreadID=TH.ThreadID
WHERE ThreadHead.ForumID='$fid'
GROUP BY ThreadID
ORDER BY TH.ThreadReplied DESC";

And this query:

$sql = "SELECT 
ThreadHead.ThreadID,
ThreadHead.ThreadBy,
ThreadHead.ThreadStarted,
ThreadHead.ThreadSubject,
ThreadHead.ForumID,
ThreadPosts.ThreadPostID,
ThreadPosts.ThreadID,
ThreadPosts.ThreadReplied,
ThreadPosts.ThreadReplyer 
FROM ThreadHead 
JOIN ThreadPosts 
on ThreadHead.ThreadID=ThreadPosts.ThreadID 
WHERE ThreadHead.ForumID='$fid' 
GROUP BY ThreadHead.ThreadID,ThreadPosts.ThreadID
ORDER BY ThreadPosts.ThreadReplied desc";

If i dont group this second one it sorts it correct but i cant get it to sort correct with group by.

so basically my question is how do i get it to sort correctly with the group by?

When you group by ThreadID, you pick a random post from the group. Then you order by that random post's date. Are you trying to get the latest post in each thread? — Barmar, Mar 31 '17 at 21:33
@Barmar, yes i've seen that thread and no it didnt make me any wiser sadly... — per källström, Mar 31 '17 at 22:17
"Groupwise max" problem: http://mysql.rjweb.org/doc.php/groupwise_max — Rick James, Apr 01 '17 at 21:58
@perkällström Edit your post, showing your attempt to implement one of the solutions in that question for your needs. Then we can reopen the question and show how to correct it. — Barmar, Apr 03 '17 at 07:56

SQL join where group by and order by, cant get it to show in the right order

0 Answers0