Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    18

    Unanswered: need to 'order by' date, but also 'order by' and integer field

    I have a table that represents comments that are posted on the wall "forum" of a member of a social network site.

    I want to select the comments so that the most recently updated topics/threads come first, but I want all the comments on a given 'topicID' to be together, so the net effect is to return first all the comments that are part of the update 'topicID' (the most recently made comment), then all the comments for the next most recently updated 'topicID', and so on

    If I do
    select * from wall_comments order by posted desc;
    this will give me the most recently updated comments. But how do get the 'topicID' to be contiguous?

    The table looks like this:
    CREATE TABLE wall_comments (commentID int unsigned NOT NULL auto_increment,
    wallmemberID int unsigned NOT NULL,
    topicID int unsigned NOT NULL,
    posterID int unsigned NOT NULL,
    posted DATETIME NOT NULL,
    comment varchar(350),
    primary key(commentID),
    unique key(wallmemberID, topicID, posterID, posted),
    constraint foreign key (wallmemberID) references member_info(memberID),
    constraint foreign key (posterID) references member_info(memberID)
    );

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ORDER BY topicID ASC, posted DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    18
    Thanks for the reply, but that isn't really what I'm after. I don't care about the order of the 'topicID'. Let me try to explain it better with an example.

    If I do this

    select UNIX_TIMESTAMP(posted) as unix_ts, topicID, posterID, first_name, last_name from wall_comments as wc
    JOIN member_info as mi
    on mi.memberID=wc.posterID ORDER BY posted DESC

    I get the comments sorted from newest to oldest.

    1266518173 7 1 Kurt Berg
    1266518053 7 33 Kurtk Jones
    1266454998 6 32 Kurt Foo
    1266454962 4 32 Kurt Foo
    1266454895 1 32 Kurt Foo
    1266454566 5 32 Kurt Foo
    1266454482 2 32 Kurt Foo
    1266453952 2 32 Kurt Foo
    1266453810 4 31 Kurt Smith
    1266453781 3 31 Kurt Smith
    1266453589 3 1 Kurt Berg
    1266453557 2 1 Kurt Berg
    1266453508 1 1 Kurt Berg
    1266453464 1 1 Kurt Berg

    But what I want is the most recent 'thread of discussion', follow by the next most recent 'thread of discussion'. So what I want is this

    1266518173 7 1 Kurt Berg <-- most recently updated thread of discussion
    1266518053 7 33 Kurtk Jones
    1266454998 6 32 Kurt Foo <--next most recently updated thread of discussion
    1266454962 4 32 Kurt Foo <--third most recently updated thread of discussion, etc.
    1266453810 4 31 Kurt Smith
    1266454895 1 32 Kurt Foo
    1266453508 1 1 Kurt Berg
    1266453464 1 1 Kurt Berg
    1266454566 5 32 Kurt Foo
    1266454482 2 32 Kurt Foo
    1266453952 2 32 Kurt Foo
    1266453781 3 31 Kurt Smith
    1266453589 3 1 Kurt Berg

    topicID 7 was most recently updated (by posterID of 1). So I want all comments pertaining to topicID 7 to come first, ordered from most recent to oldest (within topicID of 7). Next comes topicID 6 as the next most recently updated 'thread of discussion', but it only has one comment. Next topicID 4 is the third most recently updated. It has two comments. The second comment for topicID 4 has unix_ts of
    1266453810, which is older than the first comment of topicID 1, but I don't care about that.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT wc.posted
         , wc.topicID
         , wc.posterID
         , mi.first_name
         , mi.last_name 
      FROM wall_comments AS wc
    INNER
      JOIN ( SELECT topicID
                  , MAX(posted) AS latest
               FROM wall_comments
             GROUP
                 BY topicID ) AS mmm
        ON mmm.topicID = wc.topicID
    INNER
      JOIN member_info AS mi 
        ON mi.memberID = wc.posterID 
    ORDER 
        BY mmm.latest DESC
         , posted DESC
    Last edited by r937; 02-24-10 at 20:47.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    18
    Thanks for the prompt reply. The entire query is this

    select UNIX_TIMESTAMP(temp4.posted) as unix_ts, temp4.topicID, temp4.posterID, first_name, last_name from member_info as mi JOIN
    (select wc.* from wall_comments as wc JOIN
    (select * from
    (
    (select distinct wc.topicID from wall_comments as wc where wc.posterID=$memberID)
    UNION
    (select DISTINCT wc.topicID from wall_comments as wc
    JOIN
    (select mi.memberID from member_info as mi, member_login as ml, member_friends mf
    WHERE mi.memberID=ml.memberID
    AND ((mi.memberID=mf.req_member AND mf.app_member=$memberID) OR (mi.memberID=mf.app_member AND mf.req_member=$memberID))
    AND ml.date_deactivated=0 AND mf.app_date<>0) as temp1
    ON wc.posterID=temp1.memberID)
    ) as temp2
    ) as temp3
    on wc.topicID=temp3.topicID) as temp4
    on mi.memberID=temp4.posterID order by temp4.posted desc

    1.)temp1 is the set of member_info(memberID)'s that are friends of $memberID. A friendship exists is someone requested a friendship and it was approved, that is the meaning of member_friends(app_member) and member_friends(req_member). Since $memberID may have either requested the friendship or approved a requested friendship, we need to check both app_member and req_member for $memberID. We also make
    sure the prospective friends's member_login has not been flagged as deactivated and the friendship itself has not been deactivated, that member_friendship(app_date) isn't zero.

    2.)temp2 is the set of wall_comment(topicID)s on which the memberIDs in temp1 have commented

    3.)temp3 is the UNION of temp2 and those topicIDs on which $memberID has commented; that is, the set of all topicIDs on
    on which either $memberID or one of his friends has commented.

    4.)temp4 is the set of all comments, the set of all tuples from wall_comments, made on the topicIDs in temp3

    5.) The final result is the JOIN of temp4--the set of all comments on topics on which either $memberID or one of his friends
    has commented--with member_info, in order to get the first and last name of the poster.

    I am thinking that I need an additional table, to achieve what I want, something like
    wall_threads that consists of { topicID:INTEGER, last_posted: DATETIME}. I would then JOIN wall_comments with wall_threads and
    simply do 'order by last_posted desc, posted desc'?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, man, you lost me

    did you understand the technique i used in post#4?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    18
    did you understand the technique i used in post#4?
    Sorry, I hadn't run the query until just now. Yes, your query does _exactly_ what I want. THANKS!

    Question: Would a 'better' approach be to just add another table, wall_threads, that has 'topicID' and a 'last_posted' date, and JOIN it will wall_comments, like this?

    SELECT wc.posted
    , wc.topicID
    , wc.posterID
    , mi.first_name
    , mi.last_name
    FROM wall_comments AS wc
    INNER
    JOIN wall_threads as wt on wc.topicID=wt.topicID
    ORDER
    BY last_posted desc, posted desc

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kkrueck View Post
    Question: Would a 'better' approach be to just add another table, wall_threads, that has 'topicID' and a 'last_posted' date, and JOIN it will wall_comments, like this?
    not really, i mean, that's what the subquery does -- it calculates the latest post per thread "on the fly" so there's no extra table to maintain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2009
    Posts
    18
    Thanks again. Much appreciated.

Tags for this Thread

Posting Permissions

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