What would be the best way to shorten the following SQL Snippet:
SELECT a.ViewCount, b.DateCreated
FROM (SELECT COUNT(*) AS ViewCount
FROM UserViewHistory uvh1
WHERE (UserID = @UserID) AND (ViewedByUserID = @RequestingUserID)) AS a,
(SELECT TOP (1) DateCreated
FROM UserViewHistory uvh2
WHERE (UserID = @UserID) AND (ViewedByUserID = @RequestingUserID)
ORDER BY DateCreated DESC) b
The idea of the query is the pull the lastviewed date and also the number of views in total - i开发者_高级运维t works as it is, but I was wondering if there was a better way?
Thanks in advance.
Your style of SQL is a little different from what I'm used to, but this is what I'd recommend.
Select max(datecreated) as [ViewedLast], COUNT(*) AS ViewCount
FROM UserViewHistory uvh1
WHERE (UserID = @UserID) AND (ViewedByUserID = @RequestingUserID)
I do not know exactly how DateCreated behaves here, but does this maybe have the desired result?
SELECT Count(*), MAX(DateCreated)
FROM UserViewHistory
WHERE (UserID = @UserID) AND (ViewedByUserID = @RequestingUserID)
Edit: Removed GROUP BY clause, since it was not neccesary; thanks for the comments!
精彩评论