开发者

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

开发者 https://www.devze.com 2022-12-21 15:44 出处:网络
What would be the best way to shorten the following SQL Snippet: SELECTa.ViewCount, b.DateCreated FROM(SELECT COUNT(*) AS ViewCount

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!

0

精彩评论

暂无评论...
验证码 换一张
取 消