SQL for retrieving latest messages in message threads.

SQL for retrieving latest messages in message threads.

am 22.11.2007 11:18:24 von Giles

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