You are performing that select max(hireddate) for each and every row where you only need to do this once.
Anything in the select sector of a query will be evaluated for each row
Anything in the from sector is assessed only once.
You could asses the date before hand and compare to that. Date fields a pretty efficient for indexing as sorting
But there is another way for working with aggregates: Common Table Expressions (CTE)
you can predefine a table that is assessed only when it is used in the query. This makes for an efficient ordering and selecting template:
with my_cte as ( select EmployeeId,Row_Number() over (order by HireDate desc) as row
EmployeeName + CASE row WHEN 1 then '*' else '' end AS EmployeeName,
inner join my_cte on e.EmployeeId= my_cte.EmployeeId
The reason I would always run to a cte is because of how useful is can be with these kind of aggregates
For example: Say we now want the most recent employee hire from each department to have a * we can alter the cte ROW_NUMBER like so:
with my_cte as ( select EmployeeId,Row_Number() over(PARTITION BY department order by HireDate desc) as row
This change has the effect of resetting the row numbering for each department so each of the departments latest employee would show the *
Alternatively, you could perform your select in the from sector:
WHEN HireDate = aggregatevalue.date THEN ' *'
END AS EmployeeName,
Max(HireDate) as date
) as aggregatevalue
there is no table join but the aggregate table will only have 1 row so it won't be a problem