470,619 Members | 2,089 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

Issue with SELECT TOP 3 / UNIQUE

To all,

I am hoping for some insight in to this query. I have a
homepage on my Intranet that displays the three most
recent postings for a number of categories (i.e.
announcements, discussion posts, users created, calendar
events, etc). The code below finds the three most recent
posts to the discussion forums.

The problem is that if 3 users respond to the same topic
it displays the three replies. I would like it to
identify the most recent replies in unique topics so that
if 2 posts are made back to back in the same topic, it
only shows that topic once. Therefore, the homepage that
displays the three most recent postings should always
display unique topics even though the last three replies
were for the same topic.

Structure of the db is DiscCategories which have
DiscDiscussions which have DiscTopics which have
DiscReplies. (i.e. eHelp Category might have IT, Marketing
discussions. Marketing might have many topics (i.e. how
do I create an Ad) which might have many replies.)

The code I am currently using follows. I am assuming that
this may be a multi sql query using SELECT UNIQUE
tbl_Intra_DiscTopics.discTopicID but I am at a loss for
how to implement.

SELECT TOP 3 tbl_Intra_DiscReplies.discReplyDate,
tbl_Intra_DiscReplies.discTopicID,
tbl_Intra_DiscTopics.discTopicTitle,
tbl_Intra_DiscReplies.discReplyUserID,
tbl_users.userFirst, tbl_users.userLast FROM tbl_users
INNER JOIN (tbl_Intra_DiscTopics INNER JOIN
tbl_Intra_DiscReplies ON tbl_Intra_DiscTopics.discTopicID
= "tbl_Intra_DiscReplies.discTopicID) ON tbl_users.userID
= tbl_Intra_DiscReplies.discReplyUserID ORDER BY
tbl_Intra_DiscReplies.discReplyDate DESC;

Thanks,
Steve

Jul 19 '05 #1
1 1674
Hi

Instead of searching by Topic Title, I would hope that
each topic would have an ID and a parent ID (so its known
what topic the reply is linked to) so

1.) A topic with 3 back to back replies,
Select say the last 50 replies or messages in your replies
db.

Search for the top 3 most recent parent IDs in the list.
Using this, search for the most recent reply for each of
the parent IDs.

Or why not search for the top 3 messages or new topics
ordered by date where the parent iD is unique.

Not sure if this is any help to you but if it is glad to
be of some service...
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by curwen | last post: by
3 posts views Thread by Jason | last post: by
8 posts views Thread by -Karl | last post: by
10 posts views Thread by shsandeep | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.