SQL: Sub-select, sorting by aggregate review data?

MS SQL:

I have two tables, USERS and REVIEWS

The reviews table contains reviews of the users, with a link on the ID column. A user can have no reviews, or many reviews, the review is a numerical value from 1-5.

I want to perform a SELECT query on the USER table, that will only return users with reviews, and then ORDER them by the users average review value (highest reviewed users first)

Any help?
SELECT DISTINCT  user.ID
 FROM [user]
 RIGHT OUTER JOIN [Review] ON user.ID = review.modelID
 ORDER BY review.ranking DESC

Open in new window

HADDADD3Asked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
1) inner join
2) group by userid
3) average  
SELECT   user.ID  ,avg(review.ranking) as avg_review,count(*) as Num_reviews
FROM [user] 
inner JOIN [Review] 
ON user.ID = review.modelID
group by user.id
order by 2 desc,3 desc

Open in new window

0
 
Ephraim WangoyaCommented:

SELECT user.ID  
FROM [user]
INNER JOIN       (select ModelID, MAX(ranking) as Ranking
                  from [Review]
                  group by ModelID) RV ON user.ID = RV.ModelID
ranking DESC
0
 
Ephraim WangoyaCommented:
sorry

here

SELECT user.ID  , RV.Ranking
FROM [user]
INNER JOIN       (select ModelID, MAX(ranking) as Ranking
                  from [Review]
                  group by ModelID) RV ON user.ID = RV.ModelID
order by Ranking DESC
0
 
Ephraim WangoyaCommented:
We can make it cleaner

SELECT [user].ID, RV.Ranking
FROM [user]
INNER JOIN       (select ModelID, MAX(ranking) as Ranking
                  from [Review]
                  group by ModelID) RV ON [user].ID = RV.ModelID
order by Ranking DESC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.