SQL for retrieving latest messages in message threads.

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

Re: SQL for retrieving latest messages in message threads.

Giles wrote:
> 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

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


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

Re: SQL for retrieving latest messages in message threads.

"Bob Barrows [MVP]" wrote in message
news:%23dK%23KhQLIHA.3356@TK2MSFTNGP02.phx.gbl...
> Giles wrote:
>> 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
>
> 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

Thanks very much Bob