CODEWITHSUNDEEP
phpmysqlsqlgroup-by
user744116
user744116

Reputation:

MySQL query order the results in GROUP BY

I'm coding a forum system and I'm trying to get the last post in a topic. The problem is I'm grouping the results on the topic id and I can't figure out a way to get the last reply to be displayed in the grouped data.

Here is my query so far: 

   SELECT SQL_CACHE users.user_id, 
          users.username, 
          topics.title, 
          topics.topic_id,
          topics.previews,
          topics.date_added,
          posts.post_id, 
          last.username AS last_username, 
          last.user_id AS last_user_id, 
          MAX( posts.post_id ) AS last_post_id,
          posts.date_added AS last_data
     FROM `topics`
LEFT JOIN `users` ON users.user_id = topics.user_id
LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )
LEFT JOIN `users` AS last ON ( last.user_id = posts.user_id )
    WHERE fcat_id = '2'
 GROUP BY topics.topic_id

Upvotes: 2

Views: 2035

Answers (5)

dkretz
dkretz

Reputation: 37655

Take it in two easy steps.


You want to find the most recent post in each category, and
then some info related to the post.

So first get the posts of interest:

    SELECT 
        MAX(topic_id) AS topic_id,
        post_id 
    FROM posts
    WHERE fcat_id = '2'
    GROUP BY topic_id

and then get "stuff" by adding on your joins:


SELECT
    tst.topic_id,
    tst.post_id,
    stuff
FROM
(
    SELECT 
        MAX(topic_id) AS topic_id,
        post_id 
    FROM posts
    WHERE fcat_id = '2'
    GROUP BY topic_id
) AS tst
LEFT JOIN users ...
LEFT JOIN topics ...

Upvotes: 0

stormbreaker
stormbreaker

Reputation: 848

You can do something like

SELECT *, `last`.`user_id` AS last_user_id FROM
(
    SELECT users.user_id, 
          users.username, 
          topics.title, 
          topics.topic_id,
          topics.previews,
          topics.date_added,
          posts.post_id, 
          MAX( posts.post_id ) AS last_post_id,
          posts.date_added AS last_data
         FROM `topics`
    LEFT JOIN `users` ON users.user_id = topics.user_id
    LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )
        WHERE fcat_id = '2'
     GROUP BY topics.topic_id
 ) AS `tst`
LEFT JOIN `posts` ON ( posts.post_id = tst.last_post_id )
LEFT JOIN `users` AS `last` ON ( `last`.user_id = posts.post_id )

Just set your selects properly and maybe alias the posts JOIN which is outside the subquery

Upvotes: 1

boug
boug

Reputation: 1887

I think what you need to do is order your results by date_added and limit your result to 1 so it returns the most recent topic. I hope this points you to the right direction

Upvotes: 0

Oswald
Oswald

Reputation: 31685

The way that SQL works is that after your WHERE clause, a temporary result set is created. Then the items from the temporary result set are grouped.

This means you have to make sure that all items from the temporary result set, that are grouped into a single final result, share the same values for the fields you want to have (i.e. they are all the "last reply to be displayed in the grouped data").

Instead of the GROUP BY, I would use a subquery that selects the last replies to be displayed and feed that to a suitable WHERE clause.

Upvotes: 1

preinheimer
preinheimer

Reputation: 3722

You can use both group by and order by. The orderby will apply within each group.

So GROUP BY topics.topic_id ORDER BY post.timestamp

(I was a bit concerned I had the parameter order wrong, but we're good: http://dev.mysql.com/doc/refman/5.5/en/select.html)

Upvotes: 1

Related Questions