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
from Employment
)
SELECT
EmployeeName +
CASE row WHEN 1 then '*' else '' end AS EmployeeName,
EmployeeNo ,
HireDate
FROM
Employment e
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
from Employment
)
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:
SELECT
EmployeeName +
CASE
WHEN HireDate = aggregatevalue.date THEN ' *'
ELSE ''
END AS EmployeeName,
EmployeeNo ,
HireDate
FROM
Employment,
(
SELECT
Max(HireDate) as date
FROM
Employment
) as aggregatevalue
there is no table join but the aggregate table will only have 1 row so it won't be a problem