SQL Server Performance

max function with output multiple fields

Discussion in 'General DBA Questions' started by tarekj, Jan 29, 2009.

  1. tarekj Member

    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
  2. Adriaan New Member

    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)
  3. tarekj Member

    Great Adriaan.
    Many Thanks

Share This Page