Comparing count between fields - need SQL query help

Hi, thank you for your help. I have the following tables. My goal is to populate Table A with the overall rating count of Table C for each asset in Table A.
A) asset table
asset_id asset_name overall_rating
1 video1 <null>
2 video2 <null>
B) rating table
rating_id rating
1 highest
2 high
3 neutral
4 low
5 lowest
C) link_asset_to_rating (Based on how people rate the videos, the values go into this table)
asset_to_ratingID asset_id rating_id
1 1 2
2 1 3
3 1 2
4 1 5
5 1 2
6 2 1
7 2 2
8 2 3
For the scenrio in Table C, I have a query that will give me the count and the max count of the values in that table (see below). So for Asset #1, the overall rating field in Table A would be 2 since '2' appeared three times in Table C and has the most ratings for that rating_id. BUT for Asset #2, there is a 3 way tie for the rating_id. How do I create a query that will compare each count of rating_id and retrieve the highest rating for that asset? So for Asset#2, I want the value '1' to be the overall rating since "highest" is the most positive response even though there was a 3 way tie.
Please let me know if I need to clarify. Here is my count query that I made into a view and my max query of the count:
CREATE VIEW vwCountOfRatingID
AS
SELECT asset_id, Count(rating_id) as CountOfRatingID, rating_id
FROM link_asset_to_rating
GROUP BY asset_id, rating_id

SELECT Max(CountOfRatingID)
FROM vwCountOfRatingID
WHERE vwCountOfRatingID.asset_id=2;

I have a question:
How will you determine what value '1' to be the overall rating for asset #2. from your data I can see that "Highest" is the highest rating and "neutral" is higher than "low" but how SQL Server can determine that? Can we safely assume that "lower the Rating_id is higher the rating"?
If so, I believe this query will help you

;with CTE (asset_id , CountOfRatingID , Rating_id )
AS
(
SELECT asset_id, Count(rating_id) as CountOfRatingID, rating_id
FROM link_asset_to_rating
GROUP BY asset_id, rating_id
)
Select A.asset_id, a.rating_id, a.CountOfRatingID
from CTE a left join CTE b
on a.asset_id= b.asset_id and a.CountOfRatingID<=b.CountOfRatingID and a.Rating_id>b.Rating_id
where b.Rating_id is null

Please note that the first character of the code starts with semicolon.

Sir - Your assumption is correct that the lower the rating_id the higher the rating. I tried your query and it worked exactly as needed. You are a genius! Thank you. I will try to incorporate it into my other query but if I have any issues, I may need to ask for more assistance but for now, you got me off to a good start. Thank you again!

Can you explain to me how the query above works? I understand parts of it but do not fully understand how it works. Also, what if I wanted 5 to be the highest rating? How does that impact the query using the CTE above? Thank you!