Click here to Skip to main content
Rate this: bad
good
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 2:07am
Comments
ryanb31 at 11-Oct-12 7:55am
   
You could store it in a variable first and then compare to that variable.
damodara naidu betha at 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 at 11-Oct-12 13:45pm
   
Good work, my 5!
bolshie6 at 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
0 OriginalGriff 8,149
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2014
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