MySQL Query: JOIN rows of 1 table

[steve de ridder]

Hi there,

I have a table of my DB that has 3 fiels:
studentid / branch / result

So every student has 2 entries: one for his academical results and one for his sports results

1 / ACADEMIC / 13
1 / PHYSICAL / 12
2 / ACADEMIC / 17
2 / PHYSICAL / 12
3 / ACADEMIC / 10
3 / PHYSICAL / 11

I would like to create an SQL query to get all students, ordered by their academical result.

So I should get

studentid / academic / physical
2 / 17 / 12
1 / 13 / 12
3 / 10 / 11

The idea

SELECT DISTINCT studentid FROM table WHERE `branch`='ACADEMIC'
JOIN table ON studentid WHERE `branch`='PHYSICAL'
ORDER BY (result IN table WHERE branch = 'ACADEMIC')

is offcourse not working, but I don't see a way to do so. Joining these lines will make it impossible to distinguish the results from the two branches.

Could anyone please give me a help in how to do so?
Thanks a lot!

Sincerely,
Steve (Belgium)