Solved

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

Posted on 2011-03-17
6
579 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:HADDADD3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35159223

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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35159229
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35159239
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35159460
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question