>You won't be able to do it with one statement. This has got to be a classic problem althought I've never really read anything about it. I've ran in to similar situations before where I wished there was a way to do a modified "Group by" but instead of actually grouping the records you could specify which record out the group you are interested, like the top or bottom record of the group. Unfortunatly you'll have to brute-force this one as you've found one of the limitiations of SQL.

Actually...I found an answer to this particular problem after several hours of research. I found that it is possible by Inner Joining on a Sub-Query. Here is the select statement I ended up with...

     SELECT STUDENT.STUDENT_KEY, 
	   STUDENT.STUDENT_ID StudentID, 
	   STUDENT.STUDENT_FIRST_NM AS FistName, 
	   STUDENT.STUDENT_MID_INIT AS MiddleName, 
	   STUDENT.STUDENT_LAST_NM AS LastName, 
	   STUDENT.STUD_BIRTHDATE AS DOB, 
	   STUDENT.HOME_ROOM AS HomeRoom, 
	   STUDENT.LEP_DURATION AS LEPYears, 
	   LPAD(TRIM(SCHOOL_ENROLL.GRADE), 2, '0') AS Grade, 
	   DISTRICT.PROGRAM_CODE AS BedsDistrict, 
	   LOCATION.PROGRAM_CODE AS Provider, 
	   LOCATION.LOCATION_ID AS ExamLocation, 
	   SCHOOL_ENROLL.EFFECT_DATE as EnrollDate, 
     FROM STUDENT 
     INNER JOIN SCHOOL_ENROLL ON SCHOOL_ENROLL.STUDENT_KEY = STUDENT.STUDENT_KEY 
     INNER JOIN ENROLL_CODES ON ENROLL_CODES.ENROLL_KEY = SCHOOL_ENROLL.ENROLL_KEY 
     INNER JOIN DISTRICT ON DISTRICT.DISTRICT_KEY = SCHOOL_ENROLL.DISTRICT_KEY 
     INNER JOIN LOCATION ON LOCATION.LOCATION_KEY = SCHOOL_ENROLL.LOCATION_KEY 
     INNER JOIN 
   	(SELECT SCHOOL_ENROLL.STUDENT_KEY, 
	        MAX(SCHOOL_ENROLL.EFFECT_DATE) AS EFFECT_DATE 
	    FROM SCHOOL_ENROLL 
	    INNER JOIN ENROLL_CODES ON ENROLL_CODES.ENROLL_KEY = SCHOOL_ENROLL.ENROLL_KEY 
	    WHERE ENROLL_CODES.ENROLL_CODE IN ('0011', '5544', '0022', '5555', '5654') 
	    GROUP BY SCHOOL_ENROLL.STUDENT_KEY) tblEnrollRecord 
	ON tblEnrollRecord.STUDENT_KEY = STUDENT.STUDENT_KEY 
     WHERE TRIM(STUDENT.STUDENT_ID) IS NOT NULL AND 
   	  TRIM(UPPER(STUDENT.STATUS)) IN ('A', 'ACTIVE') AND 
	  SCHOOL_ENROLL.EFFECT_DATE = tblEnrollRecord.EFFECT_DATE AND 
	  ENROLL_CODES.ENROLL_CODE IN ('0011', '5544', '0022', '5555', '5654')  
     ORDER BY STUDENT.STUDENT_KEY

Hope this helps someone in the future.