Using MAX with COUNT

[shauns1]

Hi

This is what I'm trying to do:

Select Name
From tblDevelopments
Where DevelopmentID = Max(
SELECT tblPFTopics.DevelopmentID, Count(*) as TotalDev
FROM tblPFEntries, tblPFTopics, tblPFThreads
WHERE tblPFTopics.TopicID =tblPFThreads.TopicID AND tblPFThreads.ThreadID=tblPFEntries.ThreadID
Group By tblPFTopics.DevelopmentID)

But as you probably already know, I can't use MAX in the WHERE clause.

What is the best way to go about this?

Many thanks

Shaun

 

[PHV]

Perhaps this ?
Select Name
From tblDevelopments
Where DevelopmentID = (
SELECT Max(tblPFTopics.DevelopmentID)
FROM tblPFEntries, tblPFTopics, tblPFThreads
WHERE tblPFTopics.TopicID =tblPFThreads.TopicID AND tblPFThreads.ThreadID=tblPFEntries.ThreadID
)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886