Issue with SELECT TOP 3 / UNIQUE

M

MePadre

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
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top