Solved

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

Posted on 2011-03-17
6
554 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

910 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

25 Experts available now in Live!

Get 1:1 Help Now