Q:

How I can select highest review from a user?

I need to select reviews for product, but unique by user (i.e. one review from user).
With my code, I select all reviews, and I can see few reviews left by one user. 
SELECT 
    tr.reviewText, tr.reviewDate, tr.reviewRating,
    u.userName AS userName,
    u.userFirstName AS userFirstName, u.userSurname AS userSurname, 
    u.countryId AS countryId
FROM 
    tblReviews tr
INNER JOIN 
    tblOrderProduct op ON op.orderProductId = tr.orderProductId
                       AND op.productOptionId IN (SELECT productOptionId 
                                                  FROM tblProductOption
                                                  WHERE productSubCuId = 111 
                                                    AND productOptionActive = 1)
LEFT JOIN 
    tblOrder o ON o.orderId = op.orderId
LEFT JOIN 
    tblUser u ON u.userRandomId = o.userRandomId
WHERE 
    tr.reviewsStatusId = 2
ORDER BY 
    tr.reviewRating DESC, tr.reviewDate DESC
    OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

Can I get just one review from each user? 
Maybe I need select userId -> group results by userId and select one per group? [I tried to do so, but I didn't succeed :( ]

A:

You can use row_number to number the reviews and select any one like below:
;with per_user_one_review
as
(SELECT tr.reviewText, tr.reviewDate, tr.reviewRating,
        u.userName as userName,
        u.userFirstName as userFirstName, u.userSurname as userSurname, 
        u.countryId as countryId, row_number() over (partition by u.userRandomId order by tr.reviewDate desc) rn
      FROM tblReviews tr
      INNER JOIN tblOrderProduct op 
        ON op.orderProductId = tr.orderProductId
      AND op.productOptionId IN (
        SELECT productOptionId FROM tblProductOption
        WHERE productSubCuId = 111 AND productOptionActive = 1
      )
      LEFT JOIN tblOrder o ON o.orderId = op.orderId
      LEFT JOIN tblUser u ON u.userRandomId = o.userRandomId
      WHERE tr.reviewsStatusId = 2
      ORDER BY tr.reviewRating DESC, tr.reviewDate DESC
      OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
      )
select * from per_user_one_review where rn = 1

It will pick the latest review (reviewDate desc) from the user.

