SQL for retrieving latest messages in message threads.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Giles - 22 Nov 2007 12:50 GMT

IIS7, asp + VBScript site: MSAccess mdb contains "email style" messages
posted by users: fields are ID (unique), ThreadID (not unique), message,
sender etc. On my "Inbox" html page, I want to display the latest message
only from each thread.
Is there an SQL query that will retrieve just the latest message from each
thread, e.g.
ID ThreadID Other Fields...
1 1
2 1
3 1
4 2
5 1
6 2
7 3
8 4
9 4
SQL to retrieve whole records with IDs 5, 6, 7, 9.
The only way I can make it work is to retrieve all records in the table, and
use a VBScript loop to output the highest ID within each ThreadID. I don't
want to retrieve and loop through an entire table every time. I thought TOP
1 was going to be useful somewhere, but can't get it right.
Thanks, Giles

Reply to this Message

Bob Barrows [MVP] - 22 Nov 2007 13:05 GMT

> IIS7, asp + VBScript site: MSAccess mdb contains "email style"
> messages posted by users: fields are ID (unique), ThreadID (not
[quoted text clipped - 18 lines]
> every time. I thought TOP 1 was going to be useful somewhere, but
> can't get it right. Thanks, Giles

Open your database in Access and use the Query Builder to create a saved
query called MaxIDPerThreadID with this sql:
SELECT ThreadID, Max(ID) AS MaxID
FROM whateverthenameofthetableis
GROUP BY ThreadID

Then join that saved query to your table with this sql:

SELECT t.ThreadID, t.ID, ...
FROM whateverthenameofthetableis AS t Join
MaxIDPerThreadID AS m ON t.ThreadID=m.ThreadID
AND t.ID = m.MaxID

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Giles - 24 Nov 2007 11:32 GMT

>> IIS7, asp + VBScript site: MSAccess mdb contains "email style"
>> messages posted by users: fields are ID (unique), ThreadID (not
[quoted text clipped - 31 lines]
> MaxIDPerThreadID AS m ON t.ThreadID=m.ThreadID
> AND t.ID = m.MaxID

Thanks very much Bob

Reply to this Message

Webmaster Forum / ASP / Database Access / November 2007

SQL for retrieving latest messages in message threads.