SELECT M.Id,M.CategoryId,M.Title,M.Short,M.ViewCount,M.DateCreated,M.Name AS CategoryName,M.PicUrl FROM

(

SELECT

s.Id,s.CategoryId,s.Title,s.Short,s.ViewCount,s.PicUrl,s.DateCreated,n.Name,

ROW_NUMBER() OVER (PARTITION BY s.CategoryId ORDER BY s.DateCreated DESC)

AS RowNumber

FROM News AS s WITH (NOLOCK)

LEFT JOIN NewsCategory AS n ON n.Id = s.CategoryId

WHERE SiteId=@SiteId AND Status=1

)

AS M

WHERE M.RowNumber <=4 ORDER BY M.CategoryId ASC