I have two tables, Courses and Sections. The courses tables has date ranges on it so it may have a check that was good from 2006 - 2008 but had a new one implemented in 2009. I want to pull the max from that column for comparison between Courses check Sections check fields. See below. SELECT sections_year,sections_id,sections_sub,sections_numb,sections_seq,sections_check,courses_check FROM sections,courses WHERE sections_year = 2013 AND sections_sub = courses_sub AND sections_numb = courses_numb AND ssbsect_prereq_chk_method_cde != scbcrse_prereq_chk_method_cde courses_eff_year = (SELECT Max (courses_eff_year); Can anybody point me in the right direction? I know you can use a Max function without the GROUP BY but how can it be done? I tried to Google and find some solutions but haven't come up with much.
http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column So I guess to start off you need to find the highest eff_year for Courses table based on sub and numb. SELECT yt1.* FROM (SELECT * FROM [Courses]) yt1 LEFT OUTER JOIN (SELECT * FROM [Courses]) yt2 ON yt1.sub = yt2.sub AND yt1.numb = yt1.numb AND yt1.eff_year < yt2.eff_year WHERE yt2.sub is null Now you can join the Sections table to that. SELECT sec.*, cor.check FROM [Sections] sec JOIN **table above** called cor WHERE sec.year = 2013 AND sec.sub = cor.sub AND sec.numb = cor.numb AND sec.check != cor.check
