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
  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
  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