asked on

SQL Query, return newest entry per Empl ID in query that returns several results per EMPL ID

I have the following query:

SELECT DELTEK.EMPL_LAB_INFO.EMPL_ID, DELTEK.EMPL.LAST_FIRST_NAME, DELTEK.EMPL_LAB_INFO.EMPL_CLASS_CD, DELTEK.EMPL_LAB_INFO.ORG_ID,
DELTEK.EMPL_LAB_INFO.EFFECT_DT, DELTEK.EMPL_LAB_INFO.S_HRLY_SAL_CD
FROM DELTEK.EMPL_LAB_INFO INNER JOIN
DELTEK.EMPL ON DELTEK.EMPL_LAB_INFO.EMPL_ID = DELTEK.EMPL.EMPL_ID
WHERE (DELTEK.EMPL.S_EMPL_STATUS_CD = 'ACT') AND (DELTEK.EMPL.TERM_DT IS NULL)

Returns:

EMPL_ID      LAST_FIRST_NAME      EMPL_CLASS_CD      ORG_ID      EFFECT_DT      S_HRLY_SAL_CD
1001      MARK      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1001      MARK      CORE-SLRY      1.1.01      1/25/2004 0:00      S
1001      MARK      PARTNER      1.1.01      1/1/2000 0:00      S
1002      JIM      CORE-SLRY      1.1.01      2/21/2005 0:00      S
1002      JIM      PARTNER      1.1.01      1/1/2000 0:00      S
1002      JIM      CORE-SLRY      1.1.01      1/25/2004 0:00      S
1002      JIM      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1003      DENNIS      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1003      DENNIS      CORE-SLRY      1.1.01      1/25/2004 0:00      S

I need it to only return on record per EID, and that should be the record with the newest effective date.

i.e.

EMPL_ID      LAST_FIRST_NAME      EMPL_CLASS_CD      ORG_ID      EFFECT_DT      S_HRLY_SAL_CD
1001      MARK      CORE-SLRY      1.1.01      11/16/2009 0:00      S
1002      JIM      CORE-SLRY      1.1.01      2/21/2005 0:00      S
1003      DENNIS      CORE-SLRY      1.1.01      11/16/2009 0:00      S

Thanks

viralypatel

Try this

SELECT DELTEK.EMPL_LAB_INFO.EMPL_ID, DELTEK.EMPL.LAST_FIRST_NAME, DELTEK.EMPL_LAB_INFO.EMPL_CLASS_CD, DELTEK.EMPL_LAB_INFO.ORG_ID,
MAX( DELTEK.EMPL_LAB_INFO.EFFECT_DT), DELTEK.EMPL_LAB_INFO.S_HRLY_SAL_CD
FROM DELTEK.EMPL_LAB_INFO INNER JOIN
DELTEK.EMPL ON DELTEK.EMPL_LAB_INFO.EMPL_ID = DELTEK.EMPL.EMPL_ID
WHERE (DELTEK.EMPL.S_EMPL_STATUS_CD = 'ACT') AND (DELTEK.EMPL.TERM_DT IS NULL)
group by DELTEK.EMPL_LAB_INFO.EMPL_ID

ASKER CERTIFIED SOLUTION

lludden

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Guy Hengel [angelIII / a3]

please check this article out to see your options:
https://www.experts-exchange.com/A_3203.html

mikeewalton

ASKER

lluden, your query is very close, except it is returning the oldest entry for each instead of the newest.

mikeewalton

ASKER

lluden, added: DESC to the order by in
:
ROW_NUMBER() OVER (PARTITION BY DELTEK.EMPL_LAB_INFO.EMPL_ID ORDER BY DELTEK.EMPL_LAB_INFO.EFFECT_DT DESC) AS Row

and it seems to be working, let me just double check it real quick.