SQL Aggregate Question

[LiisaM]

Hello,
I have a query result that looks like:
StudentID Student Name YOG SchoolYear
200001 YARITZA SANCHEZ 2012 2009
200001 YARITZA SANCHEZ  2012 2010
200002 RAFAEL ARRENDOL 2013 2009
200002 RAFAEL ARRENDOL  2013 2010
200004 SURIA BONILLA  2013 2010
200007 BRIANNA CHAVANNESSMIT 2014 2010
200050 AARON ISRAEL 2012 2009

If I use the following code StudentID 200050 gets cutoff. What am I doing wrong?

SELECT StudentID
, rtrim(First) + ' ' + rtrim(Last) As [Student Name]
, YOG
, SchoolYear
FROM @Student1
WHERE SchoolYear = (select max(SchoolYear) from @Student1)

Thank you!!

 

[PWise]

Simple max(SchoolYear) is = 2010
200050 AARON ISRAEL 2012 2009
is only in school year 2009


do you get
200002 RAFAEL ARRENDOL 2013 2009
200001 YARITZA SANCHEZ 2012 2009

 

[Geates]

What do you mean by "cutoff"?

Also, your code suggests that student 200050 wouldn't even be returned because it does not have a SchoolYear equal to MAX(SchoolYear).

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

 

[LiisaM]

I want the result to look like:
StudentID Student Name YOG SchoolYear
200001 YARITZA SANCHEZ 2012 2010
200002 RAFAEL ARRENDOL 2013 2010
200004 SURIA BONILLA 2013 2010
200007 BRIANNA CHAVANNESSMIT 2014 2010
200050 AARON ISRAEL 2012 2009

So if there are 2 records it should take the 2010 record, but if there is only one record like 200050 then it should leave the 2009 record. I tried 2 table variables and the did a full join but then it takes 15 seconds to run.

 

[djj55]

As a side note

rtrim(First) + ' ' + rtrim(Last) As [Student Name]

may return a null if either name is null.
What I use is

ISNULL(First+' ','') + ISNULL(Last,'') As [Student Name]

which may not be the best but it works for me.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[LiisaM]

Right, that's the problem. The where clause I'm using is not including the 2009 student. I want to only max the duplicates and leave the students that are not duplicated and may fall in another grad year.


SELECT StudentID
, rtrim(First) + ' ' + rtrim(Last) As [Student Name]
, YOG
, SchoolYear
FROM @Student1
WHERE SchoolYear = (select max(SchoolYear) from @Student1)

 

[markros]

You need to link subquery with the main query

SELECT StudentID
     , rtrim(First) + ' ' + rtrim(Last) As [Student Name]
     , YOG
     , SchoolYear
FROM @Student1 S
WHERE SchoolYear = (select max(SchoolYear) from @Student1 S1
and S.StudentID = S1.StudentID)

To read more about other solutions for this common problem, please, check

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2




PluralSight Learning Library

 

[LiisaM]

Got it. That works, thank you for your help!!

 

[PWise]

try
SELECT StudentID , rtrim(First) + ' ' + rtrim(Last) As [Student Name] , YOG , SchoolYear
FROM @Student1 Student1
inner join(Select StudentID,max(SchoolYear)Msy
FROM @Student1
group by Studentid
)maxschoolyear
on Student1.StudentID =maxschoolyear.StudentID
and Student1.SchoolYear=maxschoolyear.Msy