HI All, I want to write a SQL statement with max() funcion but with multiple fields outputs, here is the case: The Data: Student CodeStartDateSerial S1226/01/20093 S1201/01/20092 S1526/01/20093 S1501/01/20092 I want to group by the student to get the max of the date, but I want to show the serial as well My SQL stmt: SELECT BusesStudentDtl.StudentCode, Max(BusesStudentsHdr.StartDate) AS MaxOfStartDate, First(BusesStudentDtl.Serial) AS Serial FROM BusesStudentsHdr INNER JOIN BusesStudentDtl ON BusesStudentsHdr.Serial = BusesStudentDtl.Serial GROUP BY BusesStudentDtl.StudentCode; the result: RptLastBusesStudents Student CodeMaxOfStartDateSerial S1226/01/20092 S1526/01/20092 the serial is wrong it should be 3, what should I do? Thanks
Do you want to show the lines with the maximum StartDate for each StudentCode? Then you move the MAX expression to a correlated subquery: SELECT t1.* FROM MyTable t1 WHERE t1.StartDate = (SELECT MAX(t2.StartDate) FROM MyTable t2 WHERE t2.StudentCode = t1.StudentCode)
