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?

Answer 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?