Click here to Skip to main content
15,894,410 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to write a query to display a ‘*’ against the row of the most recently hired employee. Here's my code. Is there any other way to achieve the same in an optimized manner?

SQL
SELECT  
	CASE WHEN HireDate =	
		(
		SELECT 
			Max(HireDate) 
		FROM 
			Employment
		) 
		THEN EmployeeName + ' ' + '*'
		
		ELSE 
			EmployeeName  
		END AS EmployeeName, 
	EmployeeNo , 
	HireDate
FROM 
	Employment
Posted
Comments
ZurdoDev 11-Oct-12 7:55am    
You could store it in a variable first and then compare to that variable.
damodara naidu betha 11-Oct-12 8:08am    
Is EmployeeNo an identity column?

1 solution

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:

SQL
with my_cte as ( -- define the cte
select EmployeeId,Row_Number() over (order by HireDate desc) as row
from Employment
) -- must be followed by another cte or a select statement
SELECT  
	EmployeeName + -- You can swap your case select to make it look a little neater
            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:
SQL
with my_cte as ( -- define the cte
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:

SQL
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
 
Share this answer
 
Comments
Maciej Los 11-Oct-12 13:45pm    
Good work, my 5!
bolshie6 12-Oct-12 1:36am    
Informative. Thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900