sickofit

nvm i found the solution... kinda not what i was looking for, but it works and its still mysql directly, not php doing the dirty job.. select from a select w/o group w/some higher limit, then group them. thanks =)

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 ok so i want to query the LAST 5 CONTENTS COMMENTED. simple as this: SELECT comments.commentid, content.title, users.username FROM comments LEFT JOIN content ON comments.itemid = content.itemid LEFT JOIN users ON comments.fromid = users.userid 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.commentid, content.title, users.username FROM comments LEFT JOIN content ON comments.itemid = content.itemid LEFT JOIN users ON comments.fromid = users.userid GROUP BY comments.itemid 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) this is the dump of a sample db, using the same conditions: -- phpMyAdmin SQL Dump -- version 2.8.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 04, 2006 at 01:23 PM -- Server version: 5.0.16 -- PHP Version: 5.1.1 -- -- Database: `test` -- -- -------------------------------------------------------- -- -- Table structure for table `comments` -- CREATE TABLE `comments` ( `commentid` int(11) NOT NULL auto_increment, `fromid` int(11) NOT NULL, `itemid` int(11) NOT NULL, `time` int(11) NOT NULL, `body` varchar(50) NOT NULL, PRIMARY KEY (`commentid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `comments` -- INSERT INTO `comments` (`commentid`, `fromid`, `itemid`, `time`, `body`) VALUES (1, 1, 1, 10, 'this is my comment'), (2, 1, 2, 11, 'this is my other comment'), (3, 2, 1, 12, 'sandras comment'), (4, 1, 2, 13, 'sandras comment another'); -- -------------------------------------------------------- -- -- Table structure for table `content` -- CREATE TABLE `content` ( `itemid` int(11) NOT NULL auto_increment, `title` varchar(50) NOT NULL, `body` varchar(100) NOT NULL, PRIMARY KEY (`itemid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `content` -- INSERT INTO `content` (`itemid`, `title`, `body`) VALUES (1, 'title of content 1', 'body of content 1'), (2, 'title of content 2', 'body of content 2'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `userid` int(11) NOT NULL auto_increment, `username` varchar(12) NOT NULL, `email` varchar(35) NOT NULL, PRIMARY KEY (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `users` -- INSERT INTO `users` (`userid`, `username`, `email`) VALUES (1, 'bob', 'bob@bob.com'), (2, 'sandra', 'sandra@sandra.com'); thanks in advance thanks in advance!!