Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

SQL SERVER Get Last Updated Column value in GROUP BY Statement

Article:
Viewed:  815 
Posted On:  5/21/2016 10:51:22 PM 
How we can get last updated column value with Group By statement in SQL Server? 

 

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:

1.png

Image 1.

Records in my Table:

2.png

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


3.png

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


5.png

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


6.png

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

 

7.png

Image 6.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us