SQL Server - Order by defined hierarchy

Question

I have, for example, the following table:

Client ID	Function
1234	RE
1234	WE
1234	SP
1234	AG
6789	AG
6789	WE

And I want to get only 1 client ID but that obeys the following order/hierarchy: If the Client ID has Function = 'SP' then I want to get this option first, if it has AG then second, the others are indifferent to the order.

Desired Output:

Client ID	Function
1234	SP
6789	AG

How can I reproduce this in a query? Thanks

    WITH cte AS 
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [CLIENT_ID]  ORDER BY FUNCTION ASC) AS rn
                ,[CLIENT_ID]
                , FUNCTION 
    FROM ope.stg_client
    ORDER BY (case when FUNCTION = 'SP' then 1 when FUNCTION = 'AG' then 1  ELSE 2  end) ASC OFFSET 0 ROWS
)
SELECT *
    FROM cte
    WHERE rn = 1

Output:

Client ID	Function
1234	AG
6789	AG

Answer 1

By doing

    WITH cte AS 
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [CLIENT_ID] ORDER BY (
                CASE FUNCTION
                    WHEN 'SP' THEN 0
                    WHEN 'AG' THEN 1
                    ELSE 2
                END) ASC) AS rn
                ,[CLIENT_ID]
                , FUNCTION 
    FROM ope.stg_client
)
SELECT *
    FROM cte
    WHERE rn = 1

I was able to get the desire output