For a messaging system on a site I need to display all messages to a specific user (1028) that has not been hidden.

In each record set I am only interested in the last record of the message set to a specific user, that is not hidden.

Here is a digram that gives an example dataset and results that I would expect, and hopefully explains it better


http://www.davidrouse.co.uk/diagram.jpg


Here is a create table & dataset for my query..

CREATE TABLE IF NOT EXISTS `messages2` (
`messageID` int(11) NOT NULL AUTO_INCREMENT,
`site_userID` int(4) DEFAULT NULL,
`root_key` int(4) NOT NULL DEFAULT '0',
`hidden` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`messageID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

INSERT INTO `messages2` (`messageID`, `site_userID`, `root_key`, `hidden`) VALUES
(1, 1028, 1, 0),
(2, 12, 1, 0),
(3, 1028, 1, 1),
(4, 12, 1, 0),
(5, 1028, 2, 0),
(6, 12, 2, 0),
(7, 1028, 2, 0),
(8, 12, 2, 0);

Hi, I am not sure what result set you need back? In the example above what message ID's do you expect the row information for?