Select row satisfying certain condition and rows next to it

Question

Let's say I have a historical table keeping who has modified data

-------------------------------------------------------------
|      ID      |  Last_Modif                  | User_Modif   | Col3, Col4...
-------------------------------------------------------------
|       1      |     2018-04-09 12:12:00      | John
|       2      |     2018-04-09 11:10:00      | Jim
|       3      |     2018-04-09 11:05:00      | Mary
|       4      |     2018-04-09 11:00:00      | John
|       5      |     2018-04-09 10:56:00      | David
|       6      |     2018-04-09 10:53:00      | John
|       7      |     2018-04-08 19:50:00      | Eric
|       8      |     2018-04-08 18:50:00      | Chris
|       9      |     2018-04-08 15:50:00      | John
|       10     |     2018-04-08 12:50:00      | Chris
----------------------------------------------------------

I would like to find the modifs done by John and previous version before he did that, to check what he had modified. For example in this scenario I would like to return row 1,2,4,5,6,7,9,10

I am thinking of ranking first based on Last_modif then do a join to pick up the next row, but somehow the result is not correct. This seems not a LAG/LEAD case since I am not picking a single value from the next row, but instead the whole next row. Any idea ?

-- sample 1000 rows with RowNumber
with TopRows as
  (select top 1000 *, ROW_NUMBER() OVER(ORDER BY Last_modif desc) RowNum from [Table])
--Reference rows : Rows modif by John
  , ModifByJohn as
  (Select * from TopRows where USER_MODIF = 'John')

  select * from ModifByJohn
  UNION
  select ModifByNext.* from ModifByJohn join TopRows ModifbyNext on ModifByJohn.RowNum + 1 = ModifByNext.RowNum
  order by RowNum

How will the code look like if we would like to return last 2 modifs before John did instead of 1 ?

The concept is OK, but don't do TOP N when validating window functions as the result is not deterministic. Why are you getting wrong results? Keep in mind that if John might have modified several records, your starting point (ModifByJohn) will have several disperse row numbers. There might be a tie in row number for several modified dates also (you should untie by other column so you get consistent results each time). — EzLo, Apr 09 '18 at 15:27
@Pettrucci : Good point. What modif would you suggest so that we take only one John if there is a chain of modification by John. e.g. John1, John2, Mary will return J, J, M and not J1, J2, J2, Mary ? @ McNets : There is no correlation, that's why I ORDER BY Last_Modif and put a ROW_NUMBER there — Kenny, Apr 10 '18 at 09:30

score 0 · Answer 1 · answered Apr 09 '18 at 15:52

Maybe you can take advantage of your current ID:

with x as
(
    select t1.*,
           (select top 1 id from tbl where id > t1.id) prev_id
    from  tbl t1
    where t1.User_Modif = 'John'
)
select * from x;
GO

ID | Last_Modif          | User_Modif | prev_id
-: | :------------------ | :--------- | ------:
 1 | 09/04/2018 12:12:00 | John       |       2
 4 | 09/04/2018 11:00:00 | John       |       5
 6 | 09/04/2018 10:53:00 | John       |       7
 9 | 08/04/2018 15:50:00 | John       |      10

with x as
(
    select t1.*,
           (select top 1 id from tbl where id > t1.id) prev_id
    from  tbl t1
    where t1.User_Modif = 'John'
)
select ID, Last_Modif, User_Modif from x
union all 
select ID, Last_Modif, User_Modif 
from tbl
where  ID in (select prev_id from x)
order by ID
GO

ID | Last_Modif          | User_Modif
-: | :------------------ | :---------
 1 | 09/04/2018 12:12:00 | John      
 2 | 09/04/2018 11:10:00 | Jim       
 4 | 09/04/2018 11:00:00 | John      
 5 | 09/04/2018 10:56:00 | David     
 6 | 09/04/2018 10:53:00 | John      
 7 | 08/04/2018 19:50:00 | Eric      
 9 | 08/04/2018 15:50:00 | John      
10 | 08/04/2018 12:50:00 | Chris

dbfiddle here

Select row satisfying certain condition and rows next to it

1 Answers1