SQLTeam.com | Weblogs | Forums

Return values on max date

Hi All,

I am trying to return one row based off the max date. Table1

In the Image: Table 1, I would like to return the PERPOS_HRP_ID and the PERPOS_SUPERVISOR_HRP_ID where the PERPOS_END_DATE is the MAX(PERPOS_END_DATE)

I tried the below code:
SELECT PERPOS_HRP_ID, PERPOS_SUPERVISOR_HRP_ID
FROM SPT_PERPOS
WHERE PERPOS_END_DATE = (
SELECT MAX(PERPOS_END_DATE) FROM SPT_PERPOS WHERE PERPOS_HRP_ID = '1259100')

It should return:
1259100 0021739

You can now use ROW_NUMBER() to do this, it's more efficient and more flexible:

SELECT S_P.PERPOS_HRP_ID, S_P.PERPOS_SUPERVISOR_HRP_ID
FROM (
    SELECT *, ROW_NUMBER() 
        OVER(PARTITION BY PERPOS_HRP_ID ORDER BY PERPOS_END_DATE DESC) AS row_num
    FROM SPT_PERPOS 
    WHERE PERPOS_HRP_ID = '1259100'
) AS S_P
WHERE row_num = 1 /*added this, OOPS, left it off before*/

Thank you. It worked.

You're welcome.

I think I left off the
WHERE row_num = 1
I've added it now.

Scott, I have one more issue. Hopefully you can help. I'm learn SQL.

I have the below code:
SELECT STC_PERSON_ID , SUM(STC_CRED) AS L27_OTHER_CREDITS,
CREDIT_TYPE_DESC,
LSMAC.STC_MARK_ACAD_CREDENTIALS

FROM SPT_STUDENT_ACAD_CRED SAC
LEFT OUTER JOIN L27_STC_MARK_ACAD_CRED LSMAC ON SAC.STUDENT_ACAD_CRED_ID = LSMAC.STUDENT_ACAD_CRED_ID
WHERE STC_CURRENT_STATUS NOT IN ('D','C')

AND STC_ACAD_LEVEL = 'GR'
AND STC_CRED_TYPE = '9'
AND STC_CRED > 0.00

AND  LSMAC.STC_MARK_ACAD_CREDENTIALS IS NOT NULL
and stc_person_id in ('0007309','0174200')
GROUP BY STC_PERSON_ID,  LSMAC.STC_MARK_ACAD_CREDENTIALS,
CREDIT_TYPE_DESC
ORDER BY STC_PERSON_ID

Again, I'm trying to return the highest STC_MARK_ACAD_CREDENTIALS for each stc_person_id. Please see image.Capture88

Thanks so much for the help.

It's not clear what you want -- the highest ACAD_CRED listed on every output row, or only the row with the highest ACAD_CRED. Based on the data show, it looks like the first one to me. Here's how to do that:

SELECT STC_PERSON_ID , SUM(STC_CRED) AS L27_OTHER_CREDITS,
CREDIT_TYPE_DESC,
(SELECT MAX(LSMAC.STC_MARK_ACAD_CREDENTIALS)
 FROM L27_STC_MARK_ACAD_CRED LSMAC 
 WHERE SAC.STUDENT_ACAD_CRED_ID = LSMAC.STUDENT_ACAD_CRED_ID) AS STC_MARK_ACAD_CREDENTIALS

FROM SPT_STUDENT_ACAD_CRED SAC
WHERE STC_CURRENT_STATUS NOT IN ('D','C')
AND STC_ACAD_LEVEL = 'GR'
AND STC_CRED_TYPE = '9'
AND STC_CRED > 0.00
and stc_person_id in ('0007309','0174200')
GROUP BY STC_PERSON_ID, CREDIT_TYPE_DESC
ORDER BY STC_PERSON_ID

I apologize Scott, I want it to return only the row with the highest ACAD_CRED please

Scott, it should only return the row that is highlighted for each person, which is the highest ACAD-CRED.