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:
Image 1.
Records in my Table:
Image 2.
Problem:
I
want to fetch record like group 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:
SELECT
TRAINERNAME, TECHNOLOGY,
MAX(CITY) AS CITY,MAX(ATTENDEES) AS STUDENT,
MAX(EVENTDATE) AS DATE
FROM
EVENT_INFORMATION GROUP BY TRAINERNAME, TECHNOLOGY
Image 3.
Problem
is here:
SELECT * FROM
EVENT_INFORMATION
SELECT
TRAINERNAME, TECHNOLOGY,
MAX(CITY) AS CITY,MAX(ATTENDEES) AS STUDENT,
MAX(EVENTDATE) AS DATE
FROM
EVENT_INFORMATION GROUP BY TRAINERNAME, TECHNOLOGY
Image 4.
So
to get required result use below SQL Statement:
SELECT T.TRAINERNAME,T.TECHNOLOGY,T.CITY, T.ATTENDEES, R.MAXDATE
FROM (
SELECT
TRAINERNAME, TECHNOLOGY,
MAX(EVENTDATE) AS MAXDATE
FROM
EVENT_INFORMATION GROUP BY TRAINERNAME, TECHNOLOGY
) R
INNER JOIN EVENT_INFORMATION T
ON T.TRAINERNAME = R.TRAINERNAME AND T.TECHNOLOGY=R.TECHNOLOGY
AND T.EVENTDATE = R.MAXDATE
Image 5.
You
can compare both SQL Query result below:
SELECT
TRAINERNAME, TECHNOLOGY,
MAX(CITY) AS CITY,MAX(ATTENDEES) AS STUDENT, MAX(EVENTDATE) AS DATE
FROM
EVENT_INFORMATION GROUP BY TRAINERNAME, TECHNOLOGY
SELECT T.TRAINERNAME,T.TECHNOLOGY,T.CITY, T.ATTENDEES, R.MAXDATE
FROM (SELECT
TRAINERNAME, TECHNOLOGY,
MAX(EVENTDATE) AS MAXDATE
FROM
EVENT_INFORMATION GROUP BY TRAINERNAME, TECHNOLOGY
) R
INNER JOIN EVENT_INFORMATION T
ON T.TRAINERNAME = R.TRAINERNAME AND T.TECHNOLOGY=R.TECHNOLOGY
AND T.EVENTDATE = R.MAXDATE
Image 6.