Melrose is one of the 'magnificent seven' best places to live in Scotland, according to a new report.