CODEWITHSUNDEEP
sql-servert-sql
Owain Esau
Owain Esau

Reputation: 1922

CTE partition not returning the correct row number - TSQL

I'm trying to get the latest file in a list of files using CTE. So far I have this:

WITH CTE AS
(
    SELECT 
        ID, TRISPATH, Document, 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Document) AS RN
    FROM 
        LinkedDocuments
)
SELECT 
    ID, Document, RN 
FROM 
    CTE 
WHERE 
    TRISPATH IS NOT NULL --AND RN!=1

It seems to work with some of the IDs it returns what I want, however some of them (around 30%) don't start at 1, they start at a random number based on the ID:

14  J:\PRS\CVs\Original CVs\2015\N\*****, ***** CL Oct 15.txt   11
14  J:\PRS\CVs\Original CVs\2015\N\*****, ***** CV Nov 15.pdf   12
14  J:\PRS\CVs\Original CVs\2015\N\*****, ***** CV Oct 15.docx  13

I cant seem to figure out why this is happening, I'm sure its something to do with the partition but I can't figure out what.

Upvotes: 0

Views: 103

Answers (1)

drf
drf

Reputation: 8699

If you have records where TRISPATH is null, they will be assigned a row number and then be filtered out in the final select.

To ensure row numbers start at 1, move the WHERE clause to the CTE:

WITH CTE AS
(
    SELECT ID, TRISPATH, Document, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Document) AS RN
    FROM LinkedDocuments
    WHERE TRISPATH IS NOT NULL 
)
SELECT ID,Document, RN FROM CTE

Upvotes: 3

Related Questions