Geek Answers Handbook

How to choose the DISTINCT option with TOP-1 Information and ordering from TOP-1 Information

I have 2 tables that look like this:

CustomerInfo(CustomterID, CustomerName)
CustomerReviews(ReviewID, CustomerID, Review, Score)

I want to search for reviews for the string and return CustomerInfo.CustomerIDand CustomerInfo.CustomerName. However, I want to show only CustomerIDand CustomerNamewith one of them CustomerReviews.Reviewsand CustomerReviews.Score. I also want to order CustomerReviews.Score.

I can’t figure out how to do this, because the client can leave a few reviews, but I only need a list of clients with the highest rating.

Any ideas?

+5
source share
3 answers

This is the biggest-n-on-group problem that has come dozens of times in Stack Overflow.

, :

WITH CustomerCTE (
  SELECT i.*, r.*, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Score DESC) AS RN
  FROM CustomerInfo i
  INNER JOIN CustomerReviews r ON i.CustomerID = r.CustomerID 
  WHERE CONTAINS(r.Review, '"search"')
)
SELECT * FROM CustomerCTE WHERE RN = 1
ORDER BY Score;

, RDBMS, :

SELECT i.*, r1.*
FROM CustomerInfo i
INNER JOIN CustomerReviews r1 ON i.CustomerID = r1.CustomerID 
  AND CONTAINS(r1.Review, '"search"')
LEFT OUTER JOIN CustomerReviews r2 ON i.CustomerID = r2.CustomerID 
  AND CONTAINS(r1.Review, '"search"')
  AND (r1.Score < r2.Score OR r1.Score = r2.Score AND r1.ReviewID < r2.ReviewID)
WHERE r2.CustomerID IS NULL
ORDER BY Score;

CONTAINS(), SQL Server, LIKE .

+5

, , .

, , . , , .

select *
from [CustomerReviews] r
where [ReviewID] =
(
    select top 1 [ReviewID]
    from [CustomerReviews] rInner
    where rInner.CustomerID = r.CustomerID
    order by Score desc
)
order by Score desc

, .

+1

,

select ci.CustomterID, ci.CustomerName, cr.Review, cr.Score
from CustomerInfo ci inner join 
(select top 1*
from CustomerReviews
where Review like '%search%'
order by Score desc) cr on ci.CustomterID = cr.CustomterID 
order by cr.Score
0

More articles:


All Articles