DISTINCT ON SPECIFIC COLUMNS ONLY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DISTINCT ON SPECIFIC COLUMNS ONLY

Author

Topic

Mr Fett
Starting Member

28 Posts

Posted - 2008-10-01 : 12:13:25

Hi all,

Apologies - I know this is fairly basic but it really has be stumped. I'm writing a simple forum system and lists all threads, the problem is I'm trying to return both the thread author and the last post author which are members in the same table - best if I give an example:

[url]http://www.nawira.freshmango.com/forums/topics/?forumID=1[/url]

MY TABLES
=========

FORUM_members
-------------
memberID
nickName

FORUM_threads
-------------
threadID
memberID
title
dateAdded

FORUM_posts
-----------
threadID
memberID
dateAdded
post

MY SQL
======



SELECT FORUM_threads.threadID, FORUM_threads.memberID, FORUM_threads.dateAdded, FORUM_threads.title, threadMember.nickname AS starterNickName, threadMember.memberID AS starterMemberID, lastPostMember.nickname AS lastPostNickName, lastPostMember.memberID AS lastPostMemberID, FORUM_posts.dateAdded AS lastPostDate 

FROM FORUM_threads INNER JOIN MEMBERS_members threadMember ON FORUM_threads.memberID = threadMember.memberID INNER JOIN FORUM_posts ON FORUM_threads.threadID = FORUM_posts.threadID INNER JOIN MEMBERS_members lastPostMember ON FORUM_posts.memberID = lastPostMember.memberID 

WHERE ORDER BY FORUM_posts.dateAdded DESC

This works but obviously returns a row for every post in the thread - I just want the last post author, last post date and thread information. I can't use DISTINCT because obviously every row is DISTINCT, unless its possible to apply DISTINCT to some of the columns returned but not all?

Thanks in advance for any help - its much appreciated!

Regards

Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 12:49:17

[code]SELECT ft.title,ft.dateAdded,fm.nickName AS lastposter,fp1.dateAdded AS lastposteddate
FROM FORUM_threads ft
INNER JOIN FORUM_posts fp1
ON fp1.threadID=ft.threadID
INNER JOIN (SELECT threadID,MAX(dateAdded) AS LatestPostDate
FROM FORUM_posts
GROUP BY threadID)fp2
ON fp2.threadID=fp1.threadID
AND fp2.LatestPostDate=fp1.dateAdded
INNER JOIN FORUM_Member fm
ON fm.memberID=fp1.memberID
[/code]

Mr Fett
Starting Member

28 Posts

Posted - 2008-10-05 : 19:00:57

quote:

Originally posted by visakh16

SELECT ft.title,ft.dateAdded,fm.nickName AS lastposter,fp1.dateAdded AS lastposteddate
FROM FORUM_threads ft
INNER JOIN FORUM_posts fp1
ON fp1.threadID=ft.threadID
INNER JOIN (SELECT threadID,MAX(dateAdded) AS LatestPostDate
            FROM FORUM_posts
            GROUP BY threadID)fp2
ON fp2.threadID=fp1.threadID
AND fp2.LatestPostDate=fp1.dateAdded
INNER JOIN FORUM_Member fm
ON fm.memberID=fp1.memberID

Thanks for the reply, Visakh - I understand how you have placed a query within this query - works great.

The only problem I have is I don't understand how I can reference the 'thread starter' - this SQL only links to the members table for the last member to post - I need both the original authors nickname and the latest posters nickname: presumably I have to inner join the members table twice?

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 00:50:45

Is this what you're looking for?

SELECT ft.title,ft.dateAdded,fm1.nickName AS ThreadStarter,fm.nickName AS lastposter,fp1.dateAdded AS lastposteddate
FROM FORUM_threads ft
INNER JOIN FORUM_posts fp1
ON fp1.threadID=ft.threadID
INNER JOIN (SELECT threadID,MAX(dateAdded) AS LatestPostDate
            FROM FORUM_posts
            GROUP BY threadID)fp2
ON fp2.threadID=fp1.threadID
AND fp2.LatestPostDate=fp1.dateAdded
INNER JOIN FORUM_Member fm
ON fm.memberID=fp1.memberID
INNER JOIN FORUM_Member fm1
ON fm1.memberID=ft.memberID

Mr Fett
Starting Member

28 Posts

Posted - 2008-10-06 : 18:09:44

Yes - great thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources