Solved

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

Posted on 2011-03-17
6
542 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
  • 3
6 Comments
 
LVL 32

Expert Comment

by:ewangoya
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:ewangoya
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:ewangoya
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now