CODEWITHSUNDEEP
sql-server
JonnyBoy
JonnyBoy

Reputation: 417

Query table and Select latest 2 rows (in SQL Server)

I have a table that logs all updates made to an application. I want to query the table and return the last update by [Timestamp] and the update before that for a different value [ITEM]. I'm struggling to figure out how to get what i need. I'm returning more than one record for each ID and don't want that. 

;WITH cte AS
(
    SELECT 
        ID, 
        LAG(ITEM) OVER (PARTITION BY ID ORDER BY timestamp DESC) AS ITEM, 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY timestamp DESC) RN 
    FROM   
        MyLoggingTable
    WHERE  
        accountid = 1234
) 
SELECT 
    cte.ID, 
    dl.ITEM, 
    DL.timestamp 
FROM
    cte 
JOIN 
    MyLoggingTable DL ON cte.ID = DL.ID 
WHERE  
    rn = 1 
    AND cte.ID IN ('id here | Sub select :( ..')

Upvotes: 1

Views: 51

Answers (1)

S3S
S3S

Reputation: 25152

Is ID unique? Because if it is, your code shouldn't return duplicates. If it isn't, you will get duplicates because you are joining back to the MyLoggingTable which isn't needed. You should just move those columns (dl.Item & dl.timestamp) into the cte and return them from the cte like you did cte.ID.

I removed the LAG since you didn't return that column in your final query.

;WITH cte AS
(
    SELECT 
        ID, 
        ITEM, 
        [timestamp], 
        --LAG(ITEM) OVER (PARTITION BY ID ORDER BY timestamp DESC) AS ITEM, 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY timestamp DESC) RN 
    FROM   
        MyLoggingTable
    WHERE  
        accountid = 1234
) 
SELECT 
    cte.ID, 
    cte.ITEM, 
    cte.timestamp 
FROM
    cte 
WHERE  
    rn = 1 
    AND cte.ID IN ('id here | Sub select :( ..')

Note, if you wanted the second to the last item, as you stated in your comments, make rn=2

Upvotes: 1

Related Questions