November 2nd, 2009, 02:41 AM
|
|
Contributing User
|
|
Join Date: Jan 2005
Location: Kent, UK
Posts: 165
Time spent in forums: 1 Day 8 h 20 m 4 sec
Reputation Power: 6
|
|
Query - General - Too many records returned?
Hi I'm struggling with this:
Code:
SELECT TOP (100) PERCENT dbo.tblBlog.blogID, dbo.tblBlog.blogDate, dbo.tblBlog.blogLatestDate,
dbo.tblUsersV2.UserFirst + N' ' + dbo.tblUsersV2.UserLast AS StarterName, dbo.tblUsersV2.OwnerID AS StarterOwnerID,
dbo.vwForumReplySummary.ReplyName, dbo.vwForumReplySummary.blogReplyDate, dbo.vwForumReplySummary.ReplyOwnerID,
MAX(dbo.vwForumReplySummary.blogID) AS Expr1
FROM dbo.vwForumReplySummary INNER JOIN
dbo.tblBlog ON dbo.vwForumReplySummary.blogID = dbo.tblBlog.blogID INNER JOIN
dbo.tblUsersV2 ON dbo.tblBlog.OwnerID = dbo.tblUsersV2.OwnerID
GROUP BY dbo.tblBlog.blogID, dbo.tblBlog.blogDate, dbo.tblBlog.blogLatestDate, dbo.tblUsersV2.UserFirst + N' ' + dbo.tblUsersV2.UserLast,
dbo.tblUsersV2.OwnerID, dbo.vwForumReplySummary.ReplyName, dbo.vwForumReplySummary.blogReplyDate,
dbo.vwForumReplySummary.ReplyOwnerID
ORDER BY dbo.tblBlog.blogLatestDate DESC, dbo.tblBlog.blogDate DESC
It's for our forum, once a thread gets more than one reply the reply list increases, but all I want it is the latest reply details any idea how I can restrict the amount of records from 'dbo.vwForumReplySummary INNER JOIN' and have it only show the latest reply if there are more than one?
Many thanks
Jon
__________________
Life is not a dress rehersal!
My hobby:
Photo4me.com
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
