SELECT COUNT(*)AS Page_Views
     , [Speciality Name]
     , [Sub Speciality Name]
     , [Page Name]
  FROM(
    SELECT s.[Speciality Name]
         , ss.[Sub Speciality Name]
         , pa.[Page Name]
		 , max(a.Date)

      FROM [Parent Site] AS p
           INNER JOIN Speciality AS s
               ON s.[Parent Site] = p.[Site Key]
           LEFT OUTER JOIN [Sub Speciality] AS ss
               ON s.[Speciality Key] = ss.Speciality
           LEFT OUTER JOIN Pages AS pa
               ON pa.[Sub Speciality] = ss.[Sub Speciality Key]
           LEFT OUTER JOIN [Page Views] AS pv
               ON pv.[Page key] = pa.[Page Key]
      WHERE pv.Date IS NOT NULL)a
  GROUP BY [Speciality Name]
         , [Sub Speciality Name]
         , [Page Name]
  ORDER BY 1 DESC;