SELECT from 2 tables where createddate is MAX

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 2005 Forums

Transact-SQL (2005)

SELECT from 2 tables where createddate is MAX

Author

Topic

amerbashoeb
Starting Member

14 Posts

Posted - 2008-07-19 : 12:42:16

Hello Everyone...i want ur help...it is regarding a SQL storedprocedure......

now

There are Two tables

ForumTable and ThreadTable

Now ForumId is the secoundary key of the ThreadTable.

ThreadTable Rows may have same ForumID

For example

I want to select ForumTopic, ForumDescription (from ForumTable) and ThreadTopic, CreatedDate (from ThreadTable)

Where Forum.ForumID = Thread.ForumID

the above where clause is good and it is working. as I said the Thread rows may have same forumIDs thats is more than one so in that case the query will display all the threads whose forumID is FORUM.FORUMID . NOW, I do not want all those threads displayed. I just want the LATEST Thread.CreatedDate row be only display....and all other earlier dates (of that ForumID) be not displayed.

To make it more clear.....the normal output is something like this

ForumId ForumTopic ForumDescp ThreadTopic createdDate

1 SONY SonyRelated sonyRemoteControlNotWorking 7/14/2008
1 SONY SonyRelated sonyTVBadImage 7/15/2008 1 SONY SonyRelated sonyhandicamNotWorking 7/16/2008
2 HP HPRelated HPNotbkNotWorking 7/12/2008
2 HP HPRelated HPPrntrNotWorking 7/10/2008
2 HP HPRelated HPMonitorNotWorking 7/09/2008

now....below is my requirement....the date below and also above is the THREADCREATED....from the THREAD TABLE...

ForumId ForumTopic ForumDescp ThreadTopic createdDate

1 SONY SonyRelated sonyhandicamNotWorking 7/16/2008
2 HP HPRelated HPNotbkNotWorking 7/12/2008

as you can see above....it displayed the latest thread created under different forumIDs (sony and HP) and thats how I want.

I hope u have understood....

From my side I have given two days of intense thought...I used variables to assign the latest createdDate...using MAX...and various techniques....the way I want it to be display is quiet similiar to this forum list....

Thanks in advance for putting some effort behind this query.

Amer Bashoeb

amerbashoeb
Starting Member

14 Posts

Posted - 2008-07-19 : 12:53:04

I have finally made it work...below is my query

SELECT F.ForumID,
F.ForumTopic,
F.ForumDescription,
--(SELECT T3.ThreadTopic FROM Thread T3 WHERE T3.ThreadID = T.ThreadID) as ThreadTopic,
max(T.CreatedDate) as ThreadCreatedDate

FROM Forum F
INNER JOIN dbo.Thread T ON T.ForumID = F.ForumID
WHERE F.ForumID = T.ForumID
Group by F.ForumID, F.ForumTopic , F.ForumDescription--, T.ThreadID

But if i uncomment the ThreadTopic and T.ThreadID the output displays all the rows from the THREAD table where Thread.forumId = Forum.ForumId. It just doesnt cares about the max (T.createdDate)...

Here I am confused. Please help

Amer Bashoeb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-19 : 12:56:29

Try this:


SELECT f.ForumTopic, f.ForumDescription, t.ThreadTopic, dt.CreatedDate
FROM Forum f
INNER JOIN ThreadTopic t
	ON f.ForumID = t.ForumID
INNER JOIN 
(
	SELECT ForumID, MAX(CreatedDate) AS CreatedDate
	FROM ThreadTopic
	GROUP BY ForumID
) dt
	ON t.ForumID = dt.ForumID AND t.CreatedDate = dt.CreatedDate

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

amerbashoeb
Starting Member

14 Posts

Posted - 2008-07-19 : 13:03:34

You are a genius mate. It worked. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 13:04:30

[code]SELECT ForumTopic, ForumDescription,ThreadTopic, CreatedDate
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY f.ForumTopic,f.ForumDescription
ORDER BY t.CreatedDate DESC) AS Seq,
f.ForumTopic, f.ForumDescription,t.ThreadTopic, t.CreatedDate
FROM ForumTable f
INNER JOIN ThreadTable t
ON t.ForumID=f.ForumID)r
WHERE r.Seq=1[/code]

amerbashoeb
Starting Member

14 Posts

Posted - 2008-07-19 : 13:12:03

Thanks Visakh. Your query also worked.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-19 : 14:47:05

Yes but the first solution is more efficient. You can see this by comparing the execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources