CODEWITHSUNDEEP
sqlsql-server
Pollyanna109
Pollyanna109

Reputation: 3

Max value at specific year with a join

I have two tables. Key is SID in both - the join is on this key First table has education data. eg SID, Year qualification obtained (YearQual) and QualLevel. There may be several rows for each SID

The second table has event data incl. year of the event (EventYear), and SID

What I need to find is the MAX QualLevel for the EventYear.

I am using SQL Server.

I have written query after query and I can get the MAX QualLevel ever (unrelated to event year) or multiple rows showing all the qualification levels >= event year when I just want the MAX at that year.

SELECT eventdata.S_ID, eventdata.eventyear,
     max (education.qual_Level) as highqual
FROM education
  left join eventdata
  ON education.S_ID = eventdata.S_ID
  WHERE  education.YearQual<= eventyear 
  GROUP BY eventyear, qual_level, eventdata.S_ID

The above code gives multiple rows

Upvotes: 0

Views: 37

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is a good place to use apply:

SELECT ed.*, e.qual_Level as highqual
FROM eventdata ed OUTER APPLY
     (SELECT TOP (1) ed.*
      FROM education e
      WHERE e.S_ID = ed.S_ID AND
            e.YearQual <= ed.eventyear 
      ORDER BY e.YearQual DESC
     ) ed;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can use row_number()

 select S_ID,eventyear,highqual from
(SELECT eventdata.S_ID, eventdata.eventyear,
     education.qual_Level as highqual,
  row_number()over(partition by eventyear order by education.qual_Level desc) rn
FROM education
  left join eventdata
  ON education.S_ID = eventdata.S_ID
  WHERE  education.YearQual<= eventyear 
) t where t.rn=1

Upvotes: 0

PSK
PSK

Reputation: 17943

What I need to find is the MAX QualLevel for the EventYear.

If you want MAX QualLevel and the EventYear, don't include eventdata.S_ID in your select and group by statement.

As you have multiple S_ID for the same eventyear, so you will get duplicate records.

Change your query like following.

SELECT  eventdata.eventyear,
        max (education.qual_Level) as highqual
FROM education
  left join eventdata
  ON education.S_ID = eventdata.S_ID
  WHERE  education.YearQual<= eventdata.eventyear 
  GROUP BY eventdata.eventyear

Upvotes: 1

Related Questions