I see, well that could be re-written using Group By, like this:
Code:
Select Max(RE.No), PR.PersonId, PR.PersonName, RE.Destination
From Person PR
Left Join Records RE on RE.PersonId = PR.PersonId
Group By PR.PersonId, PR.PersonName, RE.Destination
..however (and it's a big one), this assumes that the Destination is the same for all rows in the Records table for that person - if not you will get duplicate rows for each different value (each with a different No, as it will be the maximum for that Destination value).

The sub-query works better in this kind of situation as you want to select multiple values from a row in one of the tables, but only where a certain field (No) has a particular value (the Max for that person). I could be wrong here but I dont think that there is a 'reasonable' way (in terms of speed or complexity), if at all, to do that without a sub-query.

If you only wanted the value for No (or more fields, but all with an aggregate function like Max), then I would expect a Group By to be more efficient, and a bit easier to read IMO.