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 + ' ' + '*'
 
<pre>
    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 490
1 Sergey Alexandrovich Kryukov 325
2 ProgramFOX 265
3 Maciej Los 245
4 Andreas Gieriet 200
0 OriginalGriff 465
1 Sergey Alexandrovich Kryukov 275
2 ProgramFOX 265
3 Maciej Los 245
4 Andreas Gieriet 200


Advertise | Privacy | Mobile
Web04 | 2.8.150331.1 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2015
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