Calling SQL Stored Procedure on each record, need a set based solution

Question

I created a cursor from the one that was described on this post: SQL - Call Stored Procedure for each record

But what I really want is a set based solution. I have two tables, Tickets and WorkLogs. Each Ticket can have multiple worklogs, I just want the most recent for each ticket.

 SELECT WorkLog.WorkLogDate, WorkLog.TextEntry, Ticket.ID,
             Ticket.Summary, Ticket.Requester, Ticket.Status, Ticket.Priority, 
             Ticket.AssignedTo, Ticket.DateResolved, Ticket.TimeSpent
 FROM Ticket INNER JOIN WorkLog ON Ticket.ID = WorkLog.TicketIDRef

If I could somehow SELECT TOP (1) _WorkLog_ FROM WorkLog ORDER BY WorkLogID DESC for each Ticket.ID in Tickets I would have the set I'm looking for. I saw some similar solutions using CROSS APPLY but I'm not sure what function I would need to apply.

Any help getting my brain out of OO gear is greatly appreciated.

score 1 · Accepted Answer · answered Dec 27 '11 at 23:28

There are several ways of doing it.

1) NOT EXISTS

SELECT WorkLog.WorkLogDate, WorkLog.TextEntry, Ticket.ID,
             Ticket.Summary, Ticket.Requester, Ticket.Status, Ticket.Priority, 
             Ticket.AssignedTo, Ticket.DateResolved, Ticket.TimeSpent
 FROM Ticket INNER JOIN WorkLog ON Ticket.ID = WorkLog.TicketIDRef
WHERE not exists (SELECT 1 FROM WorkLog w2 WHERE w2.TicketIDRef = Ticket.ID AND w2.WorkLogDate > WorkLog.WorkLogDate)

2) Sub select

  SELECT 
       (
        SELECT TOP 1 
          WorkLog.TextEntry 
        FROM 
          WorkLog 
        WHERE 
          Ticket.ID = WorkLog.TicketIDRef
        ORDER BY
          WorkLog.WorkLogDate DESC
       ) as TextEntry, 
       Ticket.ID,
       Ticket.Summary, Ticket.Requester, Ticket.Status, Ticket.Priority, 
       Ticket.AssignedTo, Ticket.DateResolved, Ticket.TimeSpent
 FROM Ticket

3) Joins

SELECT WorkLog.WorkLogDate, WorkLog.TextEntry, Ticket.ID,
             Ticket.Summary, Ticket.Requester, Ticket.Status, Ticket.Priority, 
             Ticket.AssignedTo, Ticket.DateResolved, Ticket.TimeSpent
 FROM Ticket 
        INNER JOIN WorkLog ON Ticket.ID = WorkLog.TicketIDRef
        INNER JOIN (
                     SELECT 
                       max(WorkLogDate), 
                       TicketIDRef
                     FROM
                       WorkLog w2
                     GROUP BY
                       TicketIDRef
                    ) wMax ON
          WorkLog.WorkLogDate = wMax.WorkLogDate AND
          wMax.TicketIDRef = WorkLog.TicketIDRef

Thanks Jake! What would be the easiest way to measure the performance of each of these solutions? I'm sure they ALL perform better than my cursor based solution! — aBerrantJots, Dec 27 '11 at 23:57
Within SSMS you can view the execution plan for each of these, and see which of them do more expensive operations. Also, just running them all against a large enough dataset and comparing the response time should be a good indicator. — Jake Feasel, Dec 28 '11 at 00:08

Bassam Mehanni · Answer 2 · 2011-12-27T23:28:48.293

0

What you are looking for can be achieved using row_number function

SELECT ROW_NUMBER() OVER (PARTITION BY Ticket.ID ORDER BY WorkLog.ID DESC) rowNumber,
       WorkLog.WorkLogDate, WorkLog.TextEntry, Ticket.ID,
       Ticket.Summary, Ticket.Requester, Ticket.Status, Ticket.Priority, 
       Ticket.AssignedTo, Ticket.DateResolved, Ticket.TimeSpent
FROM Ticket 
     INNER JOIN WorkLog ON Ticket.ID = WorkLog.TicketIDRef
WHERE rowNumber  1

edited Dec 27 '11 at 23:28

answered Dec 27 '11 at 23:23

Bassam Mehanni

14,796
2
33
41

Thanks for the ROW_NUMBER solution as well! – aBerrantJots Dec 28 '11 at 00:01

Calling SQL Stored Procedure on each record, need a set based solution

2 Answers2