CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Subin Jacob
Subin Jacob

Reputation: 4864

Where should I add this where condition in a partition by statement

SELECT   P.*,R.PlaceCategory,Pr.RatingAverage,
         ROW_NUMBER() OVER (PARTITION BY R.PlaceCategoryId ORDER BY Pr.RatingAverage DESC) AS RatingRank
FROM     dbo.Places AS P 
         INNER JOIN
         dbo.Cities AS C
         ON P.CityId = C.CityId
         LEFT JOIN
         (SELECT   SUM(Rat.Rating) / Count(Rat.Rating) AS RatingAverage,
                   Rat.PlaceId
          FROM     PlaceReview AS Rat
          GROUP BY PlaceId) AS Pr
         ON Pr.PlaceId = P.PlaceId
         INNER JOIN
         (SELECT   TOP 2 PlaceCategoryId,PlaceCategory
          FROM     dbo.PlaceCategory
          WHERE    [Status] = 1
          ORDER BY DisplayOrder) AS R
         ON R.PlaceCategoryId = P.PlaceCategoryId
WHERE    (P.CityId = @cityId OR C.City LIKE '%'+@cityName+'%')
         AND 
         (P.[Status]=1 AND P.IsVerified = 1);

I want to add WHERE RatingRank<5. Is it possible without making this a subQuery? Sorry for the direct question.

Upvotes: 0

Views: 78

Answers (1)

TechDo
TechDo

Reputation: 18629

Add the condition in an outer block since it can't be specified in the same query.

SELECT * FROM
(
    SELECT   P.*,R.PlaceCategory,Pr.RatingAverage,
             ROW_NUMBER() OVER (PARTITION BY R.PlaceCategoryId ORDER BY Pr.RatingAverage DESC) AS RatingRank
    FROM     dbo.Places AS P 
             INNER JOIN
             dbo.Cities AS C
             ON P.CityId = C.CityId
             LEFT JOIN
             (SELECT   SUM(Rat.Rating) / Count(Rat.Rating) AS RatingAverage,
                       Rat.PlaceId
              FROM     PlaceReview AS Rat
              GROUP BY PlaceId) AS Pr
             ON Pr.PlaceId = P.PlaceId
             INNER JOIN
             (SELECT   TOP 2 PlaceCategoryId,PlaceCategory
              FROM     dbo.PlaceCategory
              WHERE    [Status] = 1
              ORDER BY DisplayOrder) AS R
             ON R.PlaceCategoryId = P.PlaceCategoryId
    WHERE    (P.CityId = @cityId OR C.City LIKE '%'+@cityName+'%')
             AND 
             (P.[Status]=1 AND P.IsVerified = 1)
)x WHERE RatingRank<5;

Upvotes: 2

Related Questions