SQLTeam.com | Weblogs | Forums

Return values on max date

#1

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

#2

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*/
#3

Thank you. It worked.

#4

You're welcome.

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

#5

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.

#6

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
#7

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

#8

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

#9

hi Artnette

Is this what you are looking for ????

please click arrow to the left for DROP Create SAMPLE Data 
drop table #sampledata 
go 

create table #sampledata
(
STC_PERSON_ID varchar(20) ,
L27_OTHER_CREDITS float,
CREDIT_TYPE_DESC varchar(10),
STC_MARK_ACAD_CREDNTIALS int 
)
go 

insert into #sampledata select '0007309',12.000000,'Waived',34428
insert into #sampledata select '0007309',3.0000000,'Waived',34429
 
insert into #sampledata select '0174200',6.000000  ,'Waived',45365
insert into #sampledata select '0174200',30.0000000,'Waived',45366
go

select * from #sampledata
go

image
image815×143 5.25 KB

It can done with MAX ???

please click arrow to the left for SQL ... 
select 
        'SQL '
	  , STC_PERSON_ID
	  , max(STC_MARK_ACAD_CREDNTIALS) 
from 
   #sampledata 
      group by 
	     STC_PERSON_ID
go

image