Find duplicate values assigned to more than one un

问题:

I need a query that can compare two columns (DBVersionKey, Desc19). Logic - If the same Desc19 is used for more than one DBVersionKey, return only those results. Below is the data range (snippet)

DATA SAMPLE

DBVersionKey    Desc19
2491            W25170206H
2492            W25170212H
2493            W25170218H
2494            W25170224H
9129            40761KB18X
9191            40761KB18X

EXPECTED RESULT
DBVersionKey    Desc19
9129            40761KB18X
9191            40761KB18X

*I have tried GROUP BY, HAVING COUNT and many other theory solutions

I do not want the query to contain any specifics to the data values, due to a potential high volume of affected records

Table name - Table_A

回答1:

WITH CTE AS (SELECT DBVersionKey, Desc19, ROW_NUMBER()OVER(PARTITION BY Desc19
ORDER BY Desc19) RN
FROM Table_A)

SELECT * FROM CTE WHERE RN > 1

I suggest using common table expressions with row number partitions. This creates a count (RN) of each instance of Desc19.

I can't test the query right now, but keep me updated on your results and I can adjust from there.

EDIT

If DBVersionKey is not a unique value, I would try:

SELECT DISTINCT DBVersionKey, Desc19, COUNT(*)
FROM Table_A
Group by DBVersionKey, Desc19

Let me know if that works better.

EDIT 2

One again, I can't test it. Adding to the first suggested query:

WITH CTE AS (SELECT DBVersionKey, Desc19, ROW_NUMBER()OVER(PARTITION BY Desc19 ORDER BY Desc19) RN 
FROM (SELECT DISTINCT DBVersionKey, Desc19 FROM Table_A GROUP BY DBVersionKey, Desc19)

SELECT * FROM CTE WHERE RN > 1

回答2:

select
    a.*
from
    Table_A as a
inner join
    (select Desc19
     from Table_A
     group by Desc19
     having count(*) > 1) as b
  on a.Desc19 = b.Desc19 ORDER BY Desc19

回答3:

Correct solution based on EDIT 2 by alybaba726

WITH CTE AS (SELECT DBVersionKey, Desc19, ROW_NUMBER()OVER(PARTITION BY Desc19
    ORDER BY Desc19) RN FROM ix_spc_planogram GROUP BY DBVersionKey, Desc19)
SELECT * FROM CTE
    WHERE RN > 1
    ORDER BY RN