Query to show last user rec in a set that's not hidden

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

here’s my attempt :slight_smile:

select * from messages2 m2
where m2.messageID in (
select max(messageID) from messages2
where site_userID = 1028
group by root_key)
and m2.hidden = 0


The inner select does the ‘potential’ records to display.
the outer select does the hidden check.