CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Bill Greer
Bill Greer

Reputation: 3156

CTE is not returning expected values

I have a table that I am trying to find duplicate rows using a Common Table Expression. The fields that I working with are as follows:

LogTime (DataType: datetime2(7),null) ControllerIP (DataType: nvchar(max),null)

I have two rows of data that have the same data in them, as far as I can tell. I did a LEN check on both columns to make sure they are equal lengths as well, yet the rows do not come back as duplicates when using the CTE below. Is there something different I need to do with the LogTime column ? I have never run into this.

WITH CTE AS 
(
SELECT rn = ROW_NUMBER() 
            OVER( 
              PARTITION BY LogTime , ControllerIP
              ORDER BY Id ASC), * 
FROM [DownTime].[dbo].[Records]
) 
SELECT * FROM cte 
WHERE   FileName = '141101.CSV' AND rn > 1 
Order By ID
GO

Also, I am using Microsoft SQL Server 2008R2.

Upvotes: 0

Views: 257

Answers (1)

Daniel Gimenez
Daniel Gimenez

Reputation: 20504

Your plan is sound. If you're not finding duplicates, it's because duplicates don't exist. You can apply some functions to the columns to make finding duplicates more likely, such as trimming spaces from the IP and reducing the precision of the datetime2.

WITH CTE AS (
    SELECT rn = ROW_NUMBER() OVER( 
        PARTITION BY CAST(LogTime AS datetime2(2)), RTRIM(LTRIM(ControllerIP))
        ORDER BY Id ASC), * 
    FROM [DownTime].[dbo].[Records]
) 
SELECT * FROM cte 
WHERE FileName = '141101.CSV' AND rn > 1 
Order By ID

Upvotes: 1

Related Questions