SQL Select statement with Inner Join help

Question

Hi,

Here is my SQL Statement which is not returning DISTINCT Thread Titles.

SELECT DISTINCT TOP 5 tblThread.Title, tblPost.Date 
FROM tblPost 
INNER JOIN tblThread ON tblPost.ThreadID = tblThread.ThreadID 
ORDER BY tblPost.Date DESC

The common field between tblThread and tblPost is ThreadID.

What I want this to do is return The latest 5 Distinct Thread Titles based on the latest 5 posts in tblPost.

Example: If a thread called ASP.NET has been posted to two times and they are the two most recent posts, the Title of the thread (ASP.NET) should only appear once and at the top of the list.

Any help would be greatly appreciated.

Stefan.

Answer 1

Try this :

SELECT DISTINCT TOP 5 tblThread.Title, MAX(tblPost.Date)
FROM tblPost INNER JOIN tblThread ON tblPost.ThreadID = tblThread.ThreadID 
GROUP BY tblThread.Title
ORDER BY MAX(tblPost.Date) DESC