How to compare two columns in SQL for multiple rows?

Question

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

how doese C is fitted in this criteria? please explain.

AK47
– AK47

2014-03-27 05:16:43 +00:00
Commented Mar 27, 2014 at 5:16 — AK47
– AK47, Commented Mar 27, 2014 at 5:16

AK47 · Accepted Answer · 2014-03-27 05:20:09Z

1

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

edited Mar 27, 2014 at 5:20

answered Mar 27, 2014 at 5:16

AK47

3,8073 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Conrad Frix Over a year ago

While this is correct it's unclear to me why you would want one inequality is the join and the other in the where. I would think you'd want either both in the join or both in the where

AK47 Over a year ago

Condition in where clause will filter records from first myTable.It is good for health of query execution plan.

Collectives™ on Stack Overflow

How to compare two columns in SQL for multiple rows?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related