Memberships ordered by the rank of membership's role.

query.sql

SELECT `memberships`.* from `memberships`
	 INNER JOIN `groups` ON `groups`.id = `memberships`.group_id
	 INNER JOIN `roles` ON `memberships`.role_id = `roles`.id
	 INNER JOIN `roles` `role_cat` ON `role_cat`.id = `roles`.type_id

WHERE `groups`.id = "HHC" AND `memberships`.expiration > CURDATE()
ORDER BY `role_cat`.rank DESC, `roles`.rank DESC