I have table of comments which includes likes and dislikes, now i have problem with proper order..
Actually my system shows comments with greatest amount of likes on top.
I'm looking for something like system in youtube.
It means that comment with 100like/100dislikes is higher in order than 1/1..
I hope this is understandable :)
This is classic problem how to rank upvote/downvote, plus/minus, like/dislike and so on. There are a few possible solutions but they may give wrong result in specific conditions.
I strongly recommend reading and using ordering like in How Not To Sort By Average Rating
PROBLEM:
You need some sort of "score" to sort by.
WRONG SOLUTION #1: Score = (Positive ratings) - (Negative ratings)
WRONG SOLUTION #2: Score = Average rating = (Positive ratings) /
(Total ratings)
CORRECT SOLUTION: Score = Lower bound of Wilson score confidence
interval for a Bernoulli parameter
Sample code (you can easily adapt it for your needs):
SELECT id, ((positive + 1.9208) / (positive + negative) -
1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) /
(positive + negative)) / (1 + 3.8416 / (positive + negative))
AS ci_lower_bound
FROM your_tab
WHERE positive + negative > 0
ORDER BY ci_lower_bound DESC;