Click here to Skip to main content
12,405,700 members (63,509 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
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?

SELECT  
	CASE WHEN HireDate =	
		(
		SELECT 
			Max(HireDate) 
		FROM 
			Employment
		) 
		THEN EmployeeName + ' ' + '*'
		
		ELSE 
			EmployeeName  
		END AS EmployeeName, 
	EmployeeNo , 
	HireDate
FROM 
	Employment
Posted 11-Oct-12 1:07am
Comments
ryanb31 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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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 ( -- 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:
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:

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
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100