CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Boris
Boris

Reputation: 607

MSSQL 2008: Get last updated record by specific field (Part 2)

Yesterday, I posted this question here: MSSQL 2008: Get last updated record by specific field

Gordon Linoff came up with a good solution and I was happy until today, when I realized I posted only half of the scenarios. Here's my new Question:

Given this table Content:

ContentId     lastUpdate              FileId    IrrelevantField
1             2014-01-01 00:00:00     File-A    Dr. Hoo     /* user uploads file*/
1             2014-01-02 00:00:00     File-B    Dr. Hoo     /* (!) user uploads new file */
1             2014-01-03 00:00:00     File-B    Dr. Who     /* user updates info */

2             2014-02-01 00:00:00     File-M    41          /* (!) user uploads file */
2             2014-02-02 00:00:00     File-M    42          /* user updates info */

3             2014-03-01 00:00:00     File-S    Donald Duck /* user uploads file*/

Basically what I want is to get all rows that meet these conditions:

IrrelevantField triggers a row update. My goal is, to get the rows, when FileId has changed.

The output would be following:

ContentId     lastUpdate              FileId    IrrelevantField
1             2014-01-02 00:00:00     File-B    Dr. Hoo
2             2014-02-01 00:00:00     File-M    41
3             2014-03-01 00:00:00     File-S    Donald Duck

FileId is never NULL.

I have tried to add a OUTER APPLY to Gordon Linoff's solution, so I can check, whether the FileId is still the same as in the initial upload. But that has gotten me irrelevant Updates as well.

Upvotes: 0

Views: 107

Answers (4)

FuzzyTree
FuzzyTree

Reputation: 32392

The query below uses lead to create a derived table containing all rows where FileId has changed plus the 1st row. It then uses row_number() to display either the 1st row if there are no changed FileIds for that ContentId (rn_asc = 1 and rn_desc = 1) or all other rows where FileId has changed (rn_asc > 1). If you just want the 1st row where FileId changed, use rn_asc = 2 instead of rn_asc > 1.

select * from (
    select * ,
    row_number() over (partition by ContentId order by lastUpdate asc) rn_asc,
    row_number() over (partition by ContentId order by lastUpdate desc) rn_desc
    from (
        select * 
        from (
            select * , 
            lead(FileId) over (partition by ContentId order by lastUpdate) previousFileId
            from content c
        ) t1 where previousFileId <> FileId or previousFileId IS NULL
    ) t2 
) t3 where (rn_asc = 1 and rn_desc = 1) or rn_asc > 1

Upvotes: 1

Renjith M Nair
Renjith M Nair

Reputation: 29

select ContentId, lastUpdate, FileId, IrrelevantField
from (  
    select  row_number() over(partition by contentid order by lastupdate desc) LastChgsl, *
    from   
    (  
        select  
        row_number() over(partition by fileid order by lastupdate) FileChgSl, *   
        from tablename  
    )V where FileChgSl = 1
)V1 where LastChgsl = 1

Upvotes: 0

GarethD
GarethD

Reputation: 69769

You were along the right lines changing it to an outer apply, then you can change the WHERE clause slightly to allow for records where there is no previous record.

SELECT  c.ContentID,
        c.LastUpdate,
        c.FileID,
        c.IrrelevantField,
        FileID2 = c2.FileID
FROM    Content AS c 
        OUTER APPLY
        (   SELECT  TOP 1 c2.FileID
            FROM    Content AS c2
            WHERE   c2.ContentID = c.ContentID
            AND     c2.LastUpdate < c.LastUpdate
            ORDER BY c2.LastUpdate DESC
        ) AS c2
WHERE   c.FileId != c2.FileId
OR      c2.FileID IS NULL;

This means though that two records are returned for ContentID = 1 (and any other content with changes):

ContentID   LastUpdate  FileID  IrrelevantField FileID2
1           2014-01-01  File-A  Dr. Hoo         NULL
1           2014-01-02  File-B  Dr. Hoo         File-A
2           2014-02-01  File-M  41              NULL
3           2014-03-01  File-S  Donald Duck     NULL

So you will need to use a further ranking function to limit this to only the latest record:

WITH CTE AS
(   SELECT  c.ContentID,
            c.LastUpdate,
            c.FileID,
            c.IrrelevantField,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY c.ContentID ORDER BY c.LastUpdate DESC)
    FROM    Content AS c 
            OUTER APPLY
            (   SELECT  TOP 1 c2.FileID
                FROM    Content AS c2
                WHERE   c2.ContentID = c.ContentID
                AND     c2.LastUpdate < c.LastUpdate
                ORDER BY c2.LastUpdate DESC
            ) AS c2
    WHERE   c.FileId != c2.FileId
    OR      c2.FileID IS NULL
)
SELECT  c.ContentID,
        c.LastUpdate,
        c.FileID,
        c.IrrelevantField
FROM    CTE AS c
WHERE   RowNumber = 1;

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93724

Try this should work with all your scenario..

;with cte
as
(
select rank() over(partition by fileid,contentid order by lastupdate ) id, ContentId,lastUpdate,FileId,IrrelevantField from tablename
)
select ContentId,lastUpdate,FileId,IrrelevantField from(
select row_number() over(partition by contentid order by lastupdate desc) fstid, * from cte where id=1) a where fstid=1

Upvotes: 2

Related Questions