473,216 Members | 1,363 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

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 1725
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: curwen | last post by:
Hi, I'm in deep struggle with query from a huge table: what I've got is a lot of records like: _______________ numb fk1 _______________ 1231456 61 1231456 62
3
by: Jason | last post by:
I will explain (or at least try to) first and then give an example after. I need to append a number of rows from several tables into one master table. Unfortunately there are certain columns...
1
by: Darius | last post by:
I was having an issue inserting data into a table in an I-Series DB2 database. The Insert statement itself is very simple: Insert into Table1 select * from Table2 These two tables have...
8
by: -Karl | last post by:
Apparently, asp.net has a posting issue with refreshing. There seems to be several solutions to the issue but I was wondering if there is a final workaround for the issue? If so, can you please...
8
by: Jeff | last post by:
hey guys. Thanks for all the help in the past. For some reason, ASP came fairly easy to me, but putting SQL in it.. well.. I lack. Anyway, here is what I have. set bam1 = conn.execute ("select...
48
by: Jimmy | last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far: <% Dim oConn, oRS, randNum Randomize() randNum = (CInt(1000 * Rnd) + 1) *...
10
by: shsandeep | last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development database while it took 2 hours to load in a UAT database. UAT db is partitioned. Dev db is not partitioned. the...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
10
by: Path9898 | last post by:
greetings. I am having an issue with a piece of code, I was hoping someone can show me where I am going wrong. Basically I select 9 rows of data out of a table that have the same item number, but...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.