SQL Server: Get Last Updated Column Value in GROUP BY Statement

In a problem statement I need to show last update column value on the base of DateTime column value by using Group By Statement:

Below is my SQL Server table:


                              Figure 1


Record in my Table:

                                                            Figure 2

Problem: I want to fetch record, such as grouping by Technology & Trainer Name but I want last updated City, Attendees &EventDate.

Yes I can use MAX(ColumnName). MAX will return correct value for EventDate but for City & Attendees it will not return.

So if I use Group By Statement:

  1. SELECT TRAINERNAME, TECHNOLOGY,MAX(CITY)AS CITY,MAX(ATTENDEES)AS STUDENT,  
  2. MAX(EVENTDATE)ASDATE  
  3. FROM EVENT_INFORMATION GROUPBY TRAINERNAME, TECHNOLOGY  

                                                   Figure 3

Problem is here:
  1. SELECT*FROM EVENT_INFORMATION  
  2.   
  3. SELECT TRAINERNAME, TECHNOLOGY,MAX(CITY)AS CITY,MAX(ATTENDEES)AS STUDENT,  
  4. MAX(EVENTDATE)ASDATE  
  5. FROM EVENT_INFORMATION GROUPBY TRAINERNAME, TECHNOLOGY   

                                                         Figure 4

So to get required result use below SQL Statement:

  1. SELECT T.TRAINERNAME,T.TECHNOLOGY,T.CITY, T.ATTENDEES, R.MAXDATE  
  2. FROM (  
  3. SELECT TRAINERNAME, TECHNOLOGY,MAX(EVENTDATE)AS MAXDATE  
  4. FROM EVENT_INFORMATION GROUPBY TRAINERNAME, TECHNOLOGY   
  5. ) R  
  6. INNERJOIN EVENT_INFORMATION T  
  7. ON T.TRAINERNAME = R.TRAINERNAME AND T.TECHNOLOGY=R.TECHNOLOGY  
  8. AND T.EVENTDATE = R.MAXDATE  

                                                      Figure 5

You can compare both SQL Query result below:
  1. SELECT TRAINERNAME, TECHNOLOGY,MAX(CITY)AS CITY,MAX(ATTENDEES)AS STUDENT,MAX(EVENTDATE)ASDATE  
  2. FROM EVENT_INFORMATION GROUPBY TRAINERNAME, TECHNOLOGY   
  3.   
  4.   
  5. SELECT T.TRAINERNAME,T.TECHNOLOGY,T.CITY, T.ATTENDEES, R.MAXDATE  
  6. FROM (SELECT TRAINERNAME, TECHNOLOGY,MAX(EVENTDATE)AS MAXDATE  
  7. FROM EVENT_INFORMATION GROUPBY TRAINERNAME, TECHNOLOGY   
  8. ) R  
  9. INNERJOIN EVENT_INFORMATION T  
  10. ON T.TRAINERNAME = R.TRAINERNAME AND T.TECHNOLOGY=R.TECHNOLOGY  
  11. AND T.EVENTDATE = R.MAXDATE  

                                                              Figure 6

 


Similar Articles