CODEWITHSUNDEEP
postgresql
gogurt
gogurt

Reputation: 831

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

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:

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

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

Upvotes: 1

Views: 20

Answers (1)

e4c5
e4c5

Reputation: 53734

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

Upvotes: 1

Related Questions