Try using GROUP BY as follows
<cfquery datasource="shared" name="Search">
Select TOP 100 B.TopicID, B.ForumID,B.LastMessageDate
From Messages A,Topics B
Where a.isapproved = 'y'
and B.TopicID = A.TopicID AND b.forumid = a.forumid
AND DateDiff(d, A.MessageDate, #TDay#) < 3
and (B.station='#thestation#' or B.station='ALL')
Group By B.TopicID, B.ForumID,B.LastMessageDate
Order By B.Lastmessagedate desc
</cfquery>
Should work
-----Original Message-----
From: Robert Forsyth [mailto:[EMAIL PROTECTED]]
Sent: 24 July 2002 14:59
To: CF-Talk
Subject: Help From You SQL Gurus Out There
Is it possible to use select TOP and distinct in the same query? I don't
want to use maxrows=100 because it returns the whole recordset and discards
records 101 and higher, which is not ideal. Here is the call:
<cfquery datasource="shared" name="Search">
Select TOP 100 Distinct B.TopicID, B.ForumID,B.LastMessageDate
From Messages A,Topics B
Where a.isapproved = 'y'
and B.TopicID = A.TopicID AND b.forumid = a.forumid
AND DateDiff(d, A.MessageDate, #TDay#) < 3
and (B.station='#thestation#' or B.station='ALL')
Order By B.Lastmessagedate desc
</cfquery>
Thanks In Advance...
Robert Forsyth
Director of Internet Operations
ABC 7
202-364-7831 (direct)
202-364-7885 (fax)
http://www.wjla.com
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
