[distinct/group by/desc problem]

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 =)

[distinct/group by/desc problem]

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!!