How to select based on values "keyed" by another column?

Question

PostgreSQL newbie here. I have data that look like this:

+-----------+---------+-------+
| StudentID | ClassID | Grade |
+-----------+---------+-------+
|     19927 | A13     |     5 |
|     19927 | A07     |     3 |
|     19927 | B22     |     7 |
|     10001 | A13     |     2 |
|     10001 | A07     |     8 |
|     22207 | A13     |     7 |
|     22207 | A07     |    10 |
|     22207 | C80     |     2 |
|     27516 | A07     |     8 |
+-----------+---------+-------+

I'm trying to select all students which have a higher grade in class A13 than in class A07. This means only including students who actually have grades in both classes.

What's the best way to do this? Having been brought up on Stata, I would normally try:

selecting only rows where classID = A07 or A13
reshaping to wide
select using a where clause on A13 > A07

But I feel like this is very un-SQL-like.

e4c5 · Accepted Answer · 2016-09-30 05:48:46Z

1

Postgresql gives lots of different ways of doing it, here's one

SELECT a13.* FROM 
   (SELECT * FROM table1 where classid='A13') as a13
INNER JOIN 
   (SELECT * FROM table1 where classid='A07') as a07
ON a13.grade > a07.grade

answered Sep 30, 2016 at 5:48

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Collectives™ on Stack Overflow

How to select based on values "keyed" by another column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related