I have code where I am getting case details. Now I need to get LogAgent name for the case.

But it is in the activity log table which have the columns CreatedBy, Type and this table has multiple rows (Logs).

Created by has different agent names and type has different values like LogComment.

I need to get first LogComment from the Type column and corresponding created by name.

Could any one please help how to do?

Below is my data and I need to get highlighted row

Sample Data

CodePudding user response：

If you just want the first Comment for a particular user

Select Top 1 *
 From  YourTable
 Where [Type]='Log Comment'
   and [CreatedBy] = 'Benn'
 Order By yourdatetimecolumn

If you want the 1st Comment for each User

Select Top 1 with ties *
 From  YourTable
 Where [Type]='Log Comment'
 Order By row_number() over (partition by CreatedBy order by yourdatetimecolumn)

Just an aside: Best to post sample data and desired results as text.