How to get one row having MAX(priority) in MSSQL?

Question

SQL

Greetings.
There are signs "Customers" and "Orders". Communication by client ID.
The Orders table also contains the Order Date and Order Priority fields.
Task: Get the customer and, if it exists, their last order with the highest priority. The client must be received in any case, even if he has no orders.
ps it is assumed that the answer will be 1 line containing information about the client and his order

Answer 1

SELECT
  ClientSet.*
FROM ClientSet LEFT JOIN (SELECT
                          *
                          ,ROW_NUMBER() OVER (PARTITION BY Client_Id ORDER BY Priority ) as rn
                         FROM OrderSet) on OrderSet.Client_Id=ClientSet .ID
WHERE OrderSet.rn=1 OR OrderSet.rn IS NULL

Seems like it should work