The above query runs very slow, i think the reason is in the order by there is the case statement,
can anyone help make this query more efficient, but still get the same result?
You're gonna need provide DDL statements for the tables (including all indexes) in order to get any advice on that.
Actual query you run (how do you use the function) together with the actual
execution plan would also help a lot.
But for the beginning I'd suggest to convert the function to a view:
Create VIEW Pupil_XY_Coordinates
SELECT saa.pupil_number, sab.address_id,
CASE WHEN sab.Housemodifier IS NULL THEN '0' ELSE 'Z' END PrecedenceOrder, sab.x_coordinate, sab.y_coordinate -- to be used for ordering in outside queries
From sps_address_bridge sab
inner join sps_stud_assignment_address saa on sab.street_number = saa.street_number
And sab.full_street_name = saa.street_name And sab.postal_code = saa.postal_code
That would allow you to select coordinates for many pupils at once without compromising performance, and also search for all pupils within some coordinates range.