CODEWITHSUNDEEP
sql
user2874403
user2874403

Reputation: 63

How to compare two columns in SQL for multiple rows?

I have a data set with four columns (author, document, rating 1, rating 2)

How do I pick authors who have written a document that has been rated higher in rating 1 than rating 2, and has also written another document that has been rated higher in rating 2 than rating 1.

Basically:

AUTHOR    DOCUMENT    RATING 1   RATING 2

A         1           1          2
B         2           1          2
B         3           3          1
C         4           2          2 
C         5           3          4
C         6           1          3
D         7           1          2
D         8           1          2

So my desired query will give me B and C because it has written docs that have had both higher and lower numbers in both ratings.

What I have:

SELECT DISTINCT author
 FROM(
 (SELECT author
 FROM table_name
 WHERE  rating1 < rating2)

UNION

(SELECT author
 FROM table_name
 WHERE  rating1 > rating2)
)
AS a

What I cant figure out is how to group the authors, test whether rating 1 and rating 2 are both higher and lower, output the name and then move on to the next group of authors. What the above prints is just the set of distinct names with either higher or lower numbers. So this one would print D as well for example.

What is my SQL code missing that would satisfy the criteria mentioned above

Upvotes: 0

Views: 418

Answers (1)

AK47
AK47

Reputation: 3797

Try this,

select * 
  from myTable as t1
       inner join MyTable as t2 
           on t1.author = t2.author
           and t2.rating1 > t2.rating2
 where t1.rating1 > t1.rating2

Upvotes: 1

Related Questions