Tutorial :MySQL: Ordering within grouping?



Question:

So I have this messages database between members in my CMS. I wanted to change the listing to show people instead of specific messages. Call it "threads" but each thread in the "my messages" page is a member that you've had a conversation with, right?

So, I obviously do this with something like this:

select id, msgfrom, headline from member.msg where msgto = $myid  group by msgfrom order by date desc  

Right, but the problem with the above is that it shows the headline of the FIRST message from each "msgfrom". So the ordering by date is applied to the date of that first message. I.e. I want the entire row returned that represents the group to obey a specific ordering.

This would be relevant if I was to fetch all texts from a database and group them by category and thus show the last text from each category. All in one query.

To make matters worse, I would also want to fetch message "threads" where the other party hasn't yet responded (so there is no msgto = $myid yet, but rather a msgfrom = $myid) and the grouping should then be on "msgto" instead of "msgfrom"

So, any ideas?


Solution:1

This would be relevant if I was to fetch all texts from a database and group them by category and thus show the last text from each category. All in one query.

This is a classic 'max per group' query that comes up on here every day. There are many, many ways to solve it that have been covered before (try searching SO) but I'll give you one example to get you started:

SELECT id, msgfrom, headline  FROM (      SELECT          id, msgfrom, headline,          @rn := CASE WHEN @prev_msgfrom = msgfrom                      THEN @rn + 1                      ELSE 1                 END AS rn,          @prev_msgfrom := msgfrom      FROM (SELECT @prev_msgfrom := NULL) vars, member.msg      ORDER BY msgfrom, headline DESC  ) T1  WHERE rn = 1  

I don't really understand the second half of your question. Can you explain it in more detail, perhaps with an example? Do you want this to be part of the same query or a separate query?


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »