--Sample Data
CREATE TABLE #TEMP
(
ID INT,
CH VARCHAR(30)
)

INSERT INTO #TEMP VALUES (1, 'Principal')
INSERT INTO #TEMP VALUES (1, 'Teacher')
INSERT INTO #TEMP VALUES (1, 'Director')
INSERT INTO #TEMP VALUES (1, 'Student')
INSERT INTO #TEMP VALUES (1, 'Something')
INSERT INTO #TEMP VALUES (1, 'Pieon')
INSERT INTO #TEMP VALUES (2, 'Student')
INSERT INTO #TEMP VALUES (2, 'Something')
INSERT INTO #TEMP VALUES (2, 'Pieon')
INSERT INTO #TEMP VALUES (3, 'Teacher')
INSERT INTO #TEMP VALUES (3, 'Pieon')
INSERT INTO #TEMP VALUES (3, 'blablabla')
INSERT INTO #TEMP VALUES (4, 'blablabla')

--Rank Table
CREATE TABLE #RANK
(
[RANK] INT,
[CH] VARCHAR(30)
)
INSERT INTO #RANK
SELECT 1,'Director'
UNION ALL SELECT 2,'Principal'
UNION ALL SELECT 3,'Teacher'
UNION ALL SELECT 4,'Student'
UNION ALL SELECT 5,'Pieon'

--Query
SELECT T.ID,T.CH
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY COALESCE(B.[RANK],99)) AS SEQ,
A.ID,A.CH,B.[RANK]
FROM #TEMP A
LEFT JOIN #RANK B ON A.[CH] = B.[CH]
) T
WHERE T.SEQ = 1

--Result
ID          CH
----------- ------------------------------
1           Director
2           Student
3           Teacher
4           blablabla