CODEWITHSUNDEEP
sqlgreatest-n-per-group
Zoelte
Zoelte

Reputation: 5

Group Comments based on two fields

I have been asked to group TX_Comments based on Enddate and CD_TYPE_COMMENTS Field. The output should be the latest row per CD_TYPE_COMMENTS and Enddate

ID_EMPLOYEE ID_HEALTH_ASSESSMENT         CD_TYPE_COMMENT    ENDDATE TX_COMMENT
0M00910044  37754   Validation          18.06.2013          Record validated.
0M00910044  37754   Validation          20.05.2013          Record validated.
0M00910044  37754   Result              26.07.2013          Created.
0M00910044  37754   Appointment         18.06.2013          Pls cancel due to                        work commitments.  
0M00910044  37754   Appointment         25.06.2013          Confirmed (via Import)
0M00910044  37754   Ellipse Difference  23.07.2013  The following Employee Details have been updated:
0M00910044  37754   General             26.07.2013  Record complete
0M00910044  37754   Validation          20.05.2013  Record created.
0M00910044  37754   Appointment         09.07.2013  As advised by Mick Maskell. To be rescheduled to another date.
0M00910044  37754   Appointment         09.07.2013  Late cancellation charges will apply.
0M00910044  37754   Appointment         09.07.2013  Confirmed (via Import)
0M00910044  37754   Ellipse Difference  23.07.2013  The following Employee Details have been updated and should be checked for impact on this HA:
0M00910044  37754   Validation          20.05.2013  Manually created due to import exception error - Test Type could not be determined. Create HA manually. Has a valid RISI.
0M00910044  37754   Appointment         25.06.2013  Preferences provided.
0M00910044  37754   Ellipse Difference  04.07.2013  The following Employee Details have been updated and should be checked for impact on this HA:
0M00910044  37754   Validation          09.07.2013  Record validated.
0M00910044  37754   Appointment         18.06.2013  Cancelled (by Railcorp)
0M00910044  37754   Appointment         09.07.2013  Cancelled (by Railcorp)
0M00910044  37754   Appointment         24.05.2013  Preferences provided.
0M00910044  37754   Appointment         28.05.2013  Confirmed (via Import)
0M00910044  37754   Appointment         09.07.2013  Preferences provided.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you want the last row, then you don't want to aggregate the data. You just want to select the last one.

Here is one method:

select t.*
from t
where t.enddate = (select max(t2.enddate)
                   from t t2
                   where t2.CD_TYPE_COMMENT = t.CD_TYPE_COMMENT
                  );

If you really want this information per employee as well, then include and t2.Employee_ID = t.Employee_ID in the correlation clause.

Upvotes: 1

Related Questions