CODEWITHSUNDEEP
sqlsql-server
Thierry
Thierry

Reputation: 6457

How to get a list of rows that have the same Id but different values in the same field

I've been playing around with different queries regarding duplicates but this is not really what I need. I do need a list of duplicates but where the value in another column is different.

I'm trying to do this in SQL Server 2012.

I need to get a list of "duplicate" rows where the DocId is the same but they have a different PoId in a table.

AuditId|DocMasterId|PoNumber
2224   |105        |11111
2374   |105        |11111
2574   |105        |11112
2624   |106        |232323
2874   |106        |242424

The query based on the above should return

105 106

But ideally, if I could list the first and last entry for each different PO based on the same DocMasterId, that would be the ideal solution, so I would end up with

AuditId|DocMasterId|PoNumber
2224   |105        |11111
2574   |105        |11112
2624   |106        |232323
2874   |106        |242424

Any ideas on how I can achieve this in SQL?

Thanks.

UPDATE:

I should have clarified that I wanted to list only rows that had a PONumber set and I wanted my results sorted by DocMasterId.

Based on Tim's answer, the final result looks like this:

WITH CTE AS
(
    SELECT AuditId, DocMasterId, PoNumber,
    RN_ASC  = ROW_NUMBER() OVER (PARTITION BY DocMasterID ORDER BY 
    PoNumber ASC),
    RN_DESC = ROW_NUMBER() OVER (PARTITION BY DocMasterID ORDER BY 
    PoNumber DESC),
    CNT = COUNT(*) OVER (PARTITION BY DocMasterID)
FROM dbo.MyTable
WHERE PONumber IS NOT NULL
)
SELECT AuditId, DocMasterId, PoNumber
FROM CTE
WHERE CNT >= 2
AND (RN_ASC = 1 OR RN_DESC = 1)
ORDER BY DocMasterId

Upvotes: 2

Views: 416

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460038

This approach uses ranking functions and a CTE:

WITH CTE AS
(
    SELECT AuditId, DocMasterId, PoNumber,
      RN_ASC  = ROW_NUMBER() OVER (PARTITION BY DocMasterID ORDER BY PoNumber ASC),
      RN_DESC = ROW_NUMBER() OVER (PARTITION BY DocMasterID ORDER BY PoNumber DESC),
      CNT = COUNT(*) OVER (PARTITION BY DocMasterID)
    FROM dbo.TableName
)
SELECT AuditId, DocMasterId, PoNumber
FROM CTE
WHERE CNT >= 2
AND (RN_ASC = 1 OR RN_DESC = 1)
ORDER BY DocMasterId

Demo

Update according your comments that NULL values in PoNumber should be excluded and not be counted for CNT:

WITH CTE AS
(
    SELECT AuditId, DocMasterId, PoNumber,
      RN_ASC  = ROW_NUMBER() OVER (PARTITION BY DocMasterID 
                                   ORDER BY CASE WHEN PoNumber IS NULL THEN 1 ELSE 0 END ASC,
                                            PoNumber ASC),
      RN_DESC = ROW_NUMBER() OVER (PARTITION BY DocMasterID ORDER BY PoNumber DESC),
      CNT = SUM(CASE WHEN PoNumber IS NOT NULL THEN 1 END) OVER (PARTITION BY DocMasterID)
    FROM dbo.TableName
)
SELECT AuditId, DocMasterId, PoNumber
FROM CTE
WHERE CNT >= 2
AND (RN_ASC = 1 OR RN_DESC = 1)
ORDER BY DocMasterId

Demo with your sample data which correctly doesn't return any recods.

Upvotes: 1

Amnesh Goel
Amnesh Goel

Reputation: 2655

select AuditId, DocMasterId, PoNumber 
from ( select *,  ROW_NUMBER() OVER (PARTITION BY DocMasterId, PoNumber ORDER BY DocMasterId ASC) as a from tablename ) abc 
where a =1

I created a partition column using DocMasterId and PoNumber which will repeat the Row_Number for every same value of DocMasterId, PoNumber. Then I eliminated duplicate records using where condition a=1

Upvotes: 2

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

Reputation: 93694

If your database supports Window function then try this

;WITH cte
     AS (SELECT Row_number()OVER(partition BY DocMasterId
                    ORDER BY AuditId DESC) a_rn,
                Row_number()OVER(partition BY DocMasterId
                    ORDER BY AuditId )     d_rn,
                *
         FROM   Yourtable)
SELECT AuditId,
       DocMasterId,
       PoNumber
FROM   cte
WHERE  a_rn = 1 or D_rn =1

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Simply GROUP BY?

select min(AuditId), DocMasterId, PoNumber
from tablename
group by DocMasterId, PoNumber

Upvotes: 0

Related Questions