Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    1

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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •