How to get perticular row from logs table

Question

Hello All,

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

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.

Could any one please help how to do?

Below is my data and I need to get highlighted row

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

I tired below query and not getting data.

Select Top 1 *
From YourTable
Where [Type]='Log Comment' and CreatedBy <> 'IT DESK'
Order By row_number() over (partition by CreatedBy order by Date )

Answer 1

It is not entirely easy to follow your question. Keep in mind that we don't know your table and business domain. It does not help that your query does not match the image you posted.

If I am to make a guess, this may be the query you are looking for:

; WITH numbering AS (
    SELECT CreatedBy, Time, Type, rowno = row_number() OVER(ORDER BY Time)
   FROM   tbl
   WHERE CreatedBy <> 'IT DESK'
     AND  Type = 'Log Comment'
)
SELECT CreatedBy, Time, Type
FROM  numbering
WHERE rowno = 1

If this does not meet your needs, I recommend that you post CREATE TABLE statements for your table and INSERT statements with sample data, and the desired result given the sample. This has two benefits:

1. It helps to clarify what you are looking for.
2. It makes it easy to copy and paste into a query window to develop a tested solution.

Answer 2

Hi ErlandSommarskog,

I tried your approach and getting only 1 case number data.
I have multiple tickets in my table.

Below is my table data and code I treid.

; WITH numbering AS (
SELECT createby ,LogTime ,activitytype,caseno ,
rowno = row_number() OVER(ORDER BY (LogTime))
FROM logsample
WHERE (activitytype IN ('Log Comment') and createby <> 'IT Desk')
and caseno in (
'345678','897689'))SELECT createby, LogTime, caseno FROM numbering WHERE rowno = 1

Table Data and result I got :

Answer 3

Hi，@Learner
Welcome to Microsoft T-SQL Q&A Forum!
Please check this:

;WITH cte_RowNum AS  
(  
 SELECT createby ,LogTime ,Activitytype,caseno,  
        ROW_NUMBER()OVER(PARTITION BY createby --Query the first logcomment of each agent  
		               --PARTITION BY caseno  (Query the first logcomment of each caseno)  
		                 ORDER BY LogTime) AS RowNum  
 FROM @test_table  
 WHERE Activitytype IN ('Log Comment') AND createby <> 'IT Desk'  
       AND caseno IN ('345678','897689')  
)  
SELECT createby,LogTime,caseno  
FROM cte_RowNum  
WHERE RowNum=1  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.