CODEWITHSUNDEEP
mysqlsqlpostgresql
Lanz Man
Lanz Man

Reputation: 15

SQL statement to determine rating of a row/record based on comparison performed among columns?

Let say, I've a table in my database like this be it in POSTGRESQL or MYSQL :-

video_clip_name | bad | so_so | good|
video 1         | 20  | 13    | 3   |
video 2         | 12  | 20    | 33  |
video 3         | 40  | 34    | 33  |
video 4         | 20  | 23    | 13  |
video 5         | 20  | 13    | 37  |

I want to determine which video is rated as bad or so_so or good based on highest score after comparison is made between the three column (bad,so_so and good). Let say, video 1 has bad rating which is higher than the other two columns..this mean that video 1 has been rated as bad.

So my question is how to write such SQL statement in POSTGRESQL/MYSQL to output the overall rating for each video based on comparison between the three columns' value?

thank you

Upvotes: 1

Views: 692

Answers (4)

eggyal
eggyal

Reputation: 125905

I come from the MySQL side, but I think the following should also work in Postgres:

SELECT video_clip_name, CASE GREATEST(bad, so_so, good)
   WHEN bad   THEN 'bad'
   WHEN so_so THEN 'so_so'
   WHEN good  THEN 'good'
END AS rating
FROM table;

However, you did not define what should happen in the event a video's highest rating score appears in more than one rating (e.g. bad = 40, so_so = 20, good = 40); in such a case the above code will always prefer the first match it encounters in the CASE (i.e. bad in preference to so_so in preference to good).

Upvotes: 3

Fred Sobotka
Fred Sobotka

Reputation: 5332

Many sites that collect ratings will compute a numeric score that represents the overall popularity of the item based on the ratings it has received. Unless you calculate some kind of numeric average, it will be difficult to compare the relative popularity of two or more items. This article from Evan Miller will help you apply a SQL formula that puts a bit of statistical rigor behind your calculations. In it, he illustrates a good solution (and a couple of not-so-good solutions) to the following problem statement:

You are a web programmer. You have users. Your users rate stuff on your site. You want to put the highest-rated stuff at the top and lowest-rated at the bottom. You need some sort of "score" to sort by.

Upvotes: 1

user756519
user756519

Reputation:

Here is another way using CASE expression. I use SQL Server and not MySQL or PostGreSQL but I think this ANSI SQL compliant. The sample data and output uses various values. However, it they are all equal it currently will pick the rating in the order bad, *so_so* and good.

Script:

CREATE TABLE dbo.VideoRatings 
(
        video_clip_name VARCHAR(10) NOT NULL
    ,   bad             INT         NOT NULL
    ,   so_so           INT         NOT NULL
    ,   good            INT         NOT NULL
);

INSERT INTO  dbo.VideoRatings (video_clip_name, bad, so_so, good) VALUES
    ('video a', 1, 2, 3),
    ('video b', 2, 3, 1),
    ('video c', 3, 2, 1),
    ('video d', 2, 1, 1),
    ('video e', 1, 2, 2),
    ('video f', 1, 1, 2),
    ('video g', 2, 2, 2);

SELECT  video_clip_name
    ,   CASE 
            WHEN bad    >= so_so    AND bad     >= good     THEN 'bad'
            WHEN so_so  >= bad      AND so_so   >= good     THEN 'so_so'
            WHEN good   >= bad      AND good    >= so_so    THEN 'good'
        END AS Rating
FROM    dbo.VideoRatings;

Output:

video_clip_name rating
--------------- ------
video a         good
video b         so_so
video c         bad
video d         bad
video e         so_so
video f         good
video g         bad

Upvotes: 0

bjorsig
bjorsig

Reputation: 1107

I am not familiar with the details of the syntax for mysql or postgresql, but to me it looks like a set of if statements, something like;

if bad > so_so then
  if bad > good then 'bad'
  else 'good'
  end if
else if so_so > good then 'so so'
else 'good'
end if

You need to have the correct select around it of course. This is assuming there is only one row for each video tape.

Upvotes: 0

Related Questions