hi! new to this forums...
ill be glad if some of you guys can help me with this:
this is the scenario.. i got a comment table for a blog right..
name of comments table is comments, name of users table is users, name of content table is items
ID(autoincr) | ITEM(parent-relation to items table) | FROM(relation to USERID table) |TIME(unixtimestamp)
ok so i want to query the LAST 5 CONTENTS COMMENTED.
simple as this:
SELECT comments.item, users.username, items.title
FROM comments
LEFT JOIN users ON comments.from = users.userid
LEFT JOIN items ON comments.item = items.itemid
ORDER BY comments.time DESC
LIMIT 5
ok so this works well, i have a result like this in the WHILE:
ITEM: 2 (for the link to the content)
USERNAME: bob (this is the author of the LAST comment, thanks to the ORDER BY)
TITLE: sample stuff (the title of the content commented)
OK SO NOW i need to have UNIQUE titles right, with the LAST COMMENT author.
so i try this:
SELECT comments.item, users.username, items.title
FROM comments
LEFT JOIN users ON comments.from = users.userid
LEFT JOIN items ON comments.item = items.itemid
GROUP BY comments.item
ORDER BY comments.time DESC
LIMIT 5
now i have unique items, BUT the last comment author isnt the last... its the first
so in "simple but wrong" words i need to "SORT" the GROUP BY.. i need to pick the LAST comment (based on TIME (unixstamp) value of each row).
mysql stops when it finds a unique row on items.. but that row is the FIRST comment, i need the LAST.
i already tried DISTINCT, works the same
this will be wonderful if ORDER BY could come BEFORE GROUP BY
is there a way to do this? (directly in mysql, i know how to do it in php but it feels like.. messy)
thanks in advance!!
(added DESC in the example.. works the same)