SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here's my attempt

    Code SQL:
    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.


Bookmarks

Posting Permissions

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