select most recent message for each forum

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

SQL Server Development (2000)

select most recent message for each forum

Author

Topic

diyeys
Starting Member

11 Posts

Posted - 2005-11-14 : 03:54:42

Hello everyone,

I have 2 tables:

- FORUM_MESSAGE

ID | ID_FORUM | ID_USER | TITLE | DATE
---------------------------------------
1 1 1 post1 10/11/2005
2 2 2 post2 09/10/2005
3 1 1 post1 08/06/2005

- FORUM_USER

ID | ID_TYPE | NAME
--------------------
1 1 admin
2 2 user

What i need to get is the most recent TITLE of each forum posted by an admin.

This is what i have done, but it is not working:
SELECT FORUM_MESSAGE.TITLE
FROM FORUM_MESSAGE, FORUM_USER
WHERE FORUM_MESSAGE.DATE = (SELECT MAX(FORUM_MESSAGE.DATE)
FROM FORUM_MESSAGE GROUP BY FORUM_MESSAGE.ID_FORUM)
AND FORUM_MESSAGE.ID_USER = FORUM_USER.ID
AND FORUM_USER.ID_TYPE = 1

Could someone help me? i am lost...

Thanks in advance

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-11-14 : 04:44:41

try this

SELECT FORUM_MESSAGE.TITLE, FORUM_MESSAGE.ID_FORUM, FORUM_MESSAGE.DATE --,FORUM_MESSAGE.ID_FORUM
FROM FORUM_MESSAGE, FORUM_USER
WHERE
FORUM_MESSAGE.ID_USER = FORUM_USER.ID
AND FORUM_USER.ID_TYPE = 1
GROUP BY FORUM_MESSAGE.TITLE, FORUM_MESSAGE.ID_FORUM, FORUM_MESSAGE.DATE
HAVING
FORUM_MESSAGE.DATE
= (SELECT MAX(A.DATE)
FROM FORUM_MESSAGE A
WHERE
A.ID_FORUM = FORUM_MESSAGE.ID_FORUM
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources