CODEWITHSUNDEEP
mysqlsqlsorting
user1760791
user1760791

Reputation: 414

SQL complex sorting by two columns

I want to use SQL to get the the 5 "best" rated profiles. The problem is, I do not want the ranking solely based on grades.

Say there is a profile with a grade 8.1, but with 5 reviews I want it to be so that a profile with a grade 7.9 and 30 reviews will be ranked higher.

How can I accomplish that if the grade is a maximum of .3 lower than another but with a minimum of 30% reviews higher is listed higher than the other?

This is my query now: (average = grade, reviews = number of reviews)

 SELECT name,reviews,average 
 FROM profiles 
 WHERE average >= 7 
    AND reviews >= 50 
 ORDER BY average  DESC, reviews DESC 
 LIMIT 5

Upvotes: 2

Views: 122

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Hmmm, I'm not sure how you implement your specific rule.

Often when faced with this task, I often subtract one standard error from the estimated value of the reviews. This gives a lower bound of the rating with some level of confidence. In general, the more reviews, the more confident, so the lower the standard deviation.

This could be readily calculated if you knew the individual reviews:

SELECT name, count(*) as numreviews, avg(reviews),
       avg(review) - std(review)/sqrt(count(*)) as avg_lb
FROM reviews r
ORDER BY avg_lb DESC
LIMIT 5;

If you are not collecting the standard deviation of the reviews for an individual, then this method will not work.

Upvotes: 1

cyk
cyk

Reputation: 581

You could do a similar calculation in the ORDER BY clause. I'll keep trying to see if I can get exactly what you're going for, but to start, you could weight your sorting something like this.

SELECT name,reviews,average 
FROM profiles 
WHERE average >= 7 
   AND reviews >= 50 
ORDER BY (average*.7 + reviews*.3) 
LIMIT 5

Upvotes: 1

AlexL
AlexL

Reputation: 1707

You could calculate on the fly the actual rating of a profile and sort by that.

Something like:

SELECT name,reviews,average, (reviews * average) AS rating 
 FROM profiles 
 WHERE average >= 7 
    AND reviews >= 50 
 ORDER BY rating DESC
 LIMIT 5

In the above example you can replace (reviews * average) with whatever algorithm you like to calculate the rating.

Keep in mind that if you have a complex algorithm the query can become slow and you may want to have a dedicated column for that and recalculate the rating every time any of the variables involved in the algorithm change.

Upvotes: 0

Related Questions