Shorten and compact this small SQL snippet (Count and most recent date)

Question

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 - it works as it is, but I was wondering if there was a better way?

Thanks in advance.

Answer 1

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!

Answer 2

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)