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

ORDER BY topicID ASC, posted DESC

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.

try this --

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'?

sorry, man, you lost me

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

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

Thanks again. Much appreciated.