SQL problem to select value based on priority

Question

I have one table Named Table1

UniqueId	Column B	Column C	Column D
1	A	Second	abc
2	A	First	def
3	A	Second	ghi

This Is Table2

Rank	Value
1	First
2	Second
3	Third

Now I have to select value like below, condition will be on Column C of Table1 and based on Table2 Like in above table1 column C has three values (Second, First, First), but First in ranked superior then second as per Table2 hence my output should look like before. Basically I have to select the superior one based on ranking. if Column C Table1 has (Third, Second, Second) so I want to select second, if Column C Table1 has (Third, Second, First) so I want to select First. if all the three value is same then that will be selected.

UniqueId	Column B	Column C	Column D	Column D
1	A	Second	abc	First
2	A	First	def	First
3	A	Second	ghi	First

I have searched a lot but I don't know how that select statement will look like.

Answer 1

You could use the analytical function first_value here:

select t.*,
  First_Value(t.c) over(partition by t.b order by r.rank) as NewRank
from t
join ranks r on r.value = t.c
order by UniqueId;