search on rank Query

SELECT TOP 30 Key_ID, Keyword, SUM(Rank) AS Expr1
FROM (SELECT Key_ID, Keyword, 7 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Nursing %') OR
(' ' + Keyword + ' ' LIKE '%-Nursing %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 5 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Home %') OR
(' ' + Keyword + ' ' LIKE '%-Home %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 3 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Government %') OR
(' ' + Keyword + ' ' LIKE '%-Government %')) AND Status = 1
UNION ALL
SELECT Key_ID, Keyword, 1 AS Rank
FROM Keyword_Master
WHERE ((' ' + Keyword + ' ' LIKE '% Approved %') OR
(' ' + Keyword + ' ' LIKE '%-Approved %')) AND Status = 1) KeywordMaster
GROUP BY Key_ID, Keyword
ORDER BY SUM(Rank) DESC