SQL select max date and corresponding value

Question

Possible Duplicate    How to get the record of a table who contains the maximum value        I ve got an aggregate query like the following   SELECT TrainingID  Max CompletedDate  as CompletedDate  Max Notes  as Notes     --This will only return the longest notes entry FROM HR EmployeeTrainings ET WHERE  ET AvantiRecID IS NULL OR ET AvantiRecID    avantiRecID  GROUP BY AvantiRecID  TrainingID               Which is working  and returns correct data most of the time  but I noticed a problem   The Notes field which gets returned will not necessarily match the record that the max completedDate  is from   Instead it will be the one with the longest string   Or the one with the highest ASCII value   What does SQL Server do in the event of a tie between two records   I m not even sure   What I want to get is the notes field from the max completedDate  record   How should I got about doing this

User · Accepted Answer

You can use a subquery   The subquery will get the Max CompletedDate   You then take this value and join on your table again to retrieve the note associate with that date   select ET1 TrainingID    ET1 CompletedDate    ET1 Notes from HR EmployeeTrainings ET1 inner join     select Max CompletedDate  CompletedDate  TrainingID   from HR EmployeeTrainings   --where AvantiRecID IS NULL OR AvantiRecID    avantiRecID   group by TrainingID   ET2   on ET1 TrainingID   ET2 TrainingID   and ET1 CompletedDate   ET2 CompletedDate where ET1 AvantiRecID IS NULL OR ET1 AvantiRecID    avantiRecID

User · Answer

Each MAX function is evaluated individually  So MAX CompletedDate  will return the value of the latest CompletedDate column and MAX Notes  will return the maximum  i e  alphabeticaly highest  value   You need to structure your query differently to get what you want  This question had actually already been asked and answered several times  so I won t repeat it   How to find the record in a table that contains the maximum value   Finding the record with maximum value in SQL

User · Answer

Ah yes  that is how it is intended in SQL  You get the Max of every column seperately  It seems like you want to return values from the row with the max date  so you have to select the row with the max date  I prefer to do this with a subselect  as the queries keep compact easy to read   SELECT TrainingID  CompletedDate  Notes FROM HR EmployeeTrainings ET  WHERE  ET AvantiRecID IS NULL OR ET AvantiRecID    avantiRecID   AND CompletedDate in      Select Max CompletedDate  from HR EmployeeTrainings B     where B TrainingID   ET TrainingID    If you also want to match by AntiRecID you should include that in the subselect as well

User · Answer

There s no easy way to do this  but something like this will work   SELECT ET TrainingID     ET CompletedDate     ET Notes FROM  HR EmployeeTrainings ET inner join     select TrainingID  Max CompletedDate  as CompletedDate   FROM HR EmployeeTrainings   WHERE  ET AvantiRecID IS NULL OR ET AvantiRecID    avantiRecID    GROUP BY AvantiRecID  TrainingID     ET2    on ET TrainingID   ET2 TrainingID   and ET CompletedDate   ET2 CompletedDate

[sql] SQL select max(date) and corresponding value

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2000