Hi All,
I am trying to return one row based off the max date. 
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*/
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.
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.
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
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
