Finding the Greatest
-
I've been working on this for some time now and just can't seem to get what I want. I have a table with two key fields and some null data in another.
Here is what I'm looking for: Return only one record for each ID. That record should be the greatest EndYear. If none of the EndYears have a year then the null EndYear record will be used. Here is my table.
ID School EndYear
1 101 2005
1 102 2006
1 103
2 102
3 103 2005
4 107 2005
4 101 2007
This is what I'd like to see:
ID School EndYear
1 102 2006
2 102
3 103 2005
4 101 2007
Any ideas?
tia
Chris
-
You could try this replacing YourTableName in both places with your table name.
SELECT OuterQuery.Id
, OuterQuery.School
, ISNULL( OuterQuery.EndYear, '' )
FROM YourTableName OuterQuery
INNER JOIN
( SELECT Id, MAX( ISNULL( EndYear, '' ) )
FROM YourTableName
GROUP BY Id
) InnerQuery
ON OuterQuery.Id = InnerQuery.Id
AND OuterQuery.EndYear = InnerQuery.EndYear
Hope it helps!
-
Thanks Julie. I did change it a little:
SELECT
OuterQuery.ID
, OuterQuery.School
, ISNULL( OuterQuery.EndYear, '' )
FROM #INSTITUTIONS_YEARS OuterQuery
INNER JOIN
( SELECT ID, MAX( ISNULL( EndYear, '' )) as EndYear
FROM #INSTITUTIONS_YEARS
GROUP BY ID
) InnerQuery
ON OuterQuery.ID = InnerQuery.ID
AND OuterQuery.EndYear = InnerQuery.EndYear
It's close, but not returning everything yet. There are some IDs in there that have no EndYear (null) associated with them at all. So I need to pull in those as well, but only if they have no schools with a EndYear.
Thanks again,
Chris
-
Whoops, my bad... That's what I get for not testing out my solutions beforehand.
SELECT
OuterQuery.ID
, OuterQuery.School
, ISNULL( OuterQuery.EndYear, '' )
FROM #INSTITUTIONS_YEARS OuterQuery
INNER JOIN
( SELECT ID, MAX( ISNULL( EndYear, '' )) as EndYear
FROM #INSTITUTIONS_YEARS
GROUP BY ID
) InnerQuery
ON OuterQuery.ID = InnerQuery.ID
AND ISNULL(OuterQuery.EndYear, '') = InnerQuery.EndYear
I think this will solve your problem. I actually tested it this time and it returns all rows that have NULLs in EndYear assuming all EndYears are NULL for that particular ID. So unless I made an incorrect assumption, I think you're all set.
-
Perfect. That works great. Thank you very much with your help on this query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply