How to get particular row from logs table-CodePudding

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

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

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

Now I need to get first Log Comment from the Activity Type column and corresponding created by name.( we need to exclude IT Desk)

Could any one please help how to do?

Below is my data and I need to get highlighted row

Data sample :

Also I have multiple tickets. I need to get this for multiple tickets.

I tired below query and not getting data.

Select Top 1 * 
From Table
Where [Type] = 'Log Comment' 
  And CreatedBy <> 'IT DESK'
  And case number in ('123','8978','5980')
Order By row_number() over (partition by CreatedBy order by Date)

CodePudding user response：

Your question is lacking a lot of details but pretty sure you are wanting something along these lines.

select CreatedBy
    , Time
    , ActivityType
from
(
    select CreatedBy
        , Time
        , ActivityType
        , RowNum = ROW_NUMBER() over(partition by CreatedBy order by Date)
    from Table
) x
where x.RowNum = 1