Geek Answers Handbook

SQL Server - select the most recent records from a group of similar records

- Scroll down for the editing I added -

So here is my script. I have a table in which there is an entry for everyone when someone changes some data. The reason for this is that we must be able to check all changes.

However, I only want to get the latest entry for a series of changes made by the user.

So, let's say there are three users: user A, B and C.

User A makes 10 changes (10 entries in the table). User B makes 5 changes User A makes 3 more changes User C makes 2 changes

I want to go back: The most recent of 2 entries created by C The last of 3 entries created by A The most recent of 5 entries that B created The most recent of 10 entries created by A

A total of 4 rows that I return

Here I tried, but the problem is that RowNum does not return to 1 when LastUpdatedBy changes:

WITH cte AS 
(
    SELECT 
        [LastUpdatedOn]
        ,[LastUpdatedBy]
        ,ROW_NUMBER() OVER(PARTITION BY [LastUpdatedBy] ORDER BY [LastUpdatedOn] DESC) [RowNum]
    FROM [HistoricalTable] 
)           
SELECT 
    [LastUpdatedOn]
    ,[LastUpdatedBy]
    ,RowNum
FROM cte
--WHERE RowNum = 1 
ORDER BY [LastUpdatedOn] DESC;

And here is the output I get (** asterisks represent the strings I want to return)

LastUpdatedOn   LastUpdatedBy   RowNum
**2011-06-07 13:07:26.917   629 1**
2011-06-07 12:57:53.700 629 2
2011-06-07 12:57:44.387 629 3
2011-06-07 12:57:34.913 629 4
2011-06-07 12:57:25.040 629 5
2011-06-07 12:57:19.927 629 6
2011-06-07 12:55:17.460 629 7
2011-06-07 12:55:12.287 629 8
2011-06-07 12:30:34.377 629 9
**2011-06-07 11:54:05.727   4   1**
**2011-06-07 11:50:02.723   629 10** (If this number went back to 1, my query would have worked fine)
2011-06-07 11:26:43.053 629 11
2011-06-07 10:54:32.867 629 12
2011-06-07 10:46:32.107 629 13
2011-06-07 10:40:52.937 629 14
**2011-06-07 10:39:50.880   3   1**

------------------- EDIT --------------------

So, I came up with a solution, but it is not very elegant and not sure if I like it, but it is a trick. This may give you a better understanding of what I'm trying to accomplish.

DECLARE @temp AS TABLE(LastUpdatedOn datetime, LastUpdatedBy int null, RowNum int);

DECLARE @newTable AS TABLE(LastUpdatedOn datetime, LastUpdatedBy int null);

DECLARE @lastUserId int = 0;

INSERT INTO @temp
SELECT 
    [LastUpdatedOn]
    ,[LastUpdatedBy]
    ,ROW_NUMBER() OVER(ORDER BY [LastUpdatedOn] DESC) [RowNum]
    FROM [HistoricalTable] 

DECLARE @totalRecords int;
SELECT @totalRecords = COUNT(*) FROM @temp;
DECLARE @counter int = 0;
WHILE @counter <= @totalRecords BEGIN
    SET @counter = @counter + 1;

    INSERT INTO @newTable
    SELECT LastUpdatedOn, LastUpdatedBy
    FROM @temp 
    WHERE RowNum = @counter AND (@lastUserId != LastUpdatedBy OR (LastUpdatedBy IS NULL));

    SELECT @lastUserId = LastUpdatedBy  FROM @temp WHERE RowNum = @counter;     
END

SELECT * FROM @newTable;

And the returned data:

LastUpdatedOn   LastUpdatedBy
2011-06-07 13:07:26.917 629
2011-06-07 11:54:05.727 4
2011-06-07 11:50:02.723 629
2011-06-07 10:39:50.880 3
+5
source share
4 answers
;with cte as
(
  select *,
    row_number() over(order by LastUpdatedOn) as rn
  from HistoricalTable
)
select C1.LastUpdatedOn,
       C1.LastUpdatedBy
from cte as C1
  left outer join cte as C2
    on C1.rn = C2.rn-1
where C1.LastUpdatedBy <> coalesce(C2.LastUpdatedBy, 0)

LastUpdatedOn , LastUpdatedBy . coalesce(C2.LastUpdatedBy, 0). , 0 , LastUpdatedBy.

+4

, - , SQL ?

declare @HistoricalTable table (LastUpdatedOn datetime, LastUpdatedBy int);

insert into @HistoricalTable (LastUpdatedOn, LastUpdatedBy) values 
('2011-06-07 13:07:26.917', 629),('2011-06-07 12:57:53.700', 629),
('2011-06-07 12:57:44.387', 629),('2011-06-07 12:57:34.913', 629),
('2011-06-07 12:57:25.040', 629),('2011-06-07 12:57:19.927', 629),
('2011-06-07 12:55:17.460', 629),('2011-06-07 12:55:12.287', 629),
('2011-06-07 12:30:34.377', 629),('2011-06-07 11:54:05.727', 4),
('2011-06-07 11:50:02.723', 629),('2011-06-07 11:26:43.053', 629),
('2011-06-07 10:54:32.867', 629),('2011-06-07 10:46:32.107', 629),
('2011-06-07 10:40:52.937', 629),('2011-06-07 10:39:50.880', 3);

select 
 latest.* 
from
(
 select *, rank() over (partition by LastUpdatedBy order by LastUpdatedOn desc) as UpdateRank 
  from @HistoricalTable
) latest
where
 latest.UpdateRank = 1
order by
 latest.LastUpdatedBy;

LastUpdatedOn           LastUpdatedBy   UpdateRank
2011-06-07 10:39:50.880            3            1
2011-06-07 11:54:05.727            4            1
2011-06-07 13:07:26.917          629            1
+2

, . :

CREATE TABLE #tmp (
 LastUpdatedBy INT,
 LastUpdatedOn DATETIME
)

INSERT  INTO #tmp
        ( LastUpdatedOn, LastUpdatedBy )
VALUES  ( '2011-06-07 13:07:26.917', 629 ),
        ( '2011-06-07 12:57:53.700', 629 ),
        ( '2011-06-07 12:57:44.387', 629 ),
        ( '2011-06-07 12:57:34.913', 629 ),
        ( '2011-06-07 12:57:25.040', 629 ),
        ( '2011-06-07 12:57:19.927', 629 ),
        ( '2011-06-07 12:55:17.460', 629 ),
        ( '2011-06-07 12:55:12.287', 629 ),
        ( '2011-06-07 12:30:34.377', 629 ),
        ( '2011-06-07 11:54:05.727', 4 ),
        ( '2011-06-07 11:50:02.723', 629 ),
        ( '2011-06-07 11:26:43.053', 629 ),
        ( '2011-06-07 10:54:32.867', 629 ),
        ( '2011-06-07 10:46:32.107', 629 ),
        ( '2011-06-07 10:40:52.937', 629 ),
        ( '2011-06-07 10:39:50.880', 3 ) ;

WITH    cte
          AS ( SELECT   [LastUpdatedOn],
                        [LastUpdatedBy],
                        ROW_NUMBER() OVER ( PARTITION BY [LastUpdatedBy] ORDER BY [LastUpdatedOn] DESC ) - ROW_NUMBER() OVER ( ORDER BY [LastUpdatedOn] DESC ) AS [Island]
               FROM     #tmp
             ),
        cte2
          AS ( SELECT   *,
                        ROW_NUMBER() OVER ( PARTITION BY [Island] ORDER BY [LastUpdatedOn] DESC ) AS [rn]
               FROM     cte
             )
    SELECT  [LastUpdatedOn],
            [LastUpdatedBy]
    FROM    cte2
    WHERE   [rn] = 1
    ORDER BY [LastUpdatedOn] DESC ;

"" , , , .

+1

, :

SELECT
    [Outer].[LastUpdatedOn],
    [Outer].[LastUpdatedBy]
FROM [HistoricalTable] AS [Outer]
WHERE NOT EXISTS
(
    SELECT *
    FROM [HistoricalTable] AS [Middle]
    WHERE [Middle].[LastUpdatedBy] = [Outer].[LastUpdatedBy]
        AND [Middle].[LastUpdatedOn] > [Outer].[LastUpdatedOn]
        AND [Middle].[LastUpdatedOn] <= ISNULL(
        (
            SELECT
                MIN([Inner].[LastUpdatedOn])
            FROM [HistoricalTable] AS [Inner]
            WHERE [Inner].[LastUpdatedBy] != [Outer].[LastUpdatedBy]
                AND [Inner].[LastUpdatedOn] > [Outer].[LastUpdatedOn]
        ), [Middle].[LastUpdatedOn])
)

, , , , , .

For each row in the table, it ensures that no other row (s) of the same user exists between the context row and the oldest row, which is later than the context row associated with another user.

0
source

More articles:


All Articles