We help IT Professionals succeed at work.
Get Started

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

336 Views
Last Modified: 2012-05-12
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

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE