SQL query filtering

Question

Hey,

Using SQL Server 2005, I have a table where certain events are being logged, and I need to create a query that returns only very specific results. There's an example below:

Log:
Log_ID | FB_ID |   Date    | Log_Name | Log_Type

   7   |   4   | 2007/11/8 |   Nina   |  Critical
   6   |   4   | 2007/11/6 |   John   |  Critical
   5   |   4   | 2007/11/6 |   Mike   |  Critical
   4   |   4   | 2007/11/6 |   Mike   |  Critical
   3   |   3   | 2007/11/3 |   Ben    |  Critical
   2   |   3   | 2007/11/1 |   Ben    |  Critical

The query should do the following: return ONLY one row per each FB_ID, but this needs to be the one where Log_Name has changed for the first time, or if the name never changes, then the first dated row.

In layman's terms I need this to browse through a DB to check for each instance where the responsibility of a case (FB_ID) has been moved to another person, and in case it never has, then just get the original logger's name.

In the example above, I should get rows (Log_ID) 2 and 6.

Is this even possible? Right now there's a discussion going on whether the DB was just made the wrong way. :)

I imagine I need to somehow be able to store the first resulting Log_Name into a variable and then compare it with an IF condition etc. I have no idea how to do such a thing with SQL though.

Edit: Updated the date. And to clarify on this, the correct result would look like this:

Log_ID | FB_ID |   Date    | Log_Name | Log_Type

   6   |   4   | 2007/11/6 |   John   |  Critical
   2   |   3   | 2007/11/1 |   Ben    |  Critical

It's not the first date per FB_ID I'm after, but the row where the Log_Name is changed from the original.

Originally FB_ID 4 belongs to Mike, but the query should return the row where it moves on to John. However, it should NOT return the row where it moves further on to Nina, because the first responsibility change already happened when John got it.

In the case of Ben with FB_ID 3, the logger is never changed, so the first row for Ben should be returned.

Answer 1

If I've understood the problem correctly, the following SQL should do the trick:

SELECT Log_ID, FB_ID, min(Date), Log_Name, Log_Type  
FROM Log  
GROUP BY Date

The SQL will select the row with the earliest date for each FP_ID.

Answer 2

I guess that there is a better and more performant way, but this one seems to work:

SELECT *
  FROM log
 WHERE log_id IN
   ( SELECT MIN(log_id)
       FROM log
      WHERE
         ( SELECT COUNT(DISTINCT log_name)
                FROM log log2
               WHERE log2.fb_id = log.fb_id ) = 1
         OR log.log_name <> ( SELECT log_name
                                FROM log log_3
                               WHERE log_3.log_id =
                                 ( SELECT MIN(log_id)
                                     FROM log log4
                                    WHERE log4.fb_id = log.fb_id ) )
      GROUP BY fb_id )

Answer 3

This will efficiently use an index on (fb_id, cdate, id):

SELECT  lo4.*
FROM
    (
    SELECT CASE WHEN ln.log_id IS NULL THEN lo2.log_id ELSE ln.log_id END AS log_id,
      ROW_NUMBER() OVER (PARTITION BY lo2.fb_id ORDER BY lo2.cdate) AS rn
    FROM (
     SELECT
      lo.*,
      (
      SELECT TOP 1 log_id
      FROM t_log li
      WHERE li.fb_id = lo.fb_id
        AND li.cdate >= lo.cdate
        AND li.log_id <> lo.log_id
        AND li.log_name <> lo.log_name
      ORDER BY
       cdate, log_id
      ) AS next_id
     FROM t_log lo
     ) lo2
    LEFT OUTER JOIN
     t_log ln
    ON ln.log_id = lo2.next_id
    ) lo3, t_log lo4
WHERE lo3.rn = 1
    AND lo4.log_id = lo3.log_id