Finding duplicate rows based on value in different column

Question

I've this table with the following data:

CaseID|ProductNr|CodeNr|Test
    1 |1234     |444   |No
    1 |1235     |444   |No
    1 |1236     |444   |No
    1 |1237     |666   |No
    1 |1238     |777   |No
    2 |1244     |555   |No
    2 |1245     |555   |No

I want a query that find all the "ProductNr" that has the same "CodeNr" within the same "CaseID".

E.g. if .. Where CaseId = 1 the result should be as below:

CaseID|ProductNr|CodeNr|Test
    1 |1234     |444   |No
    1 |1235     |444   |No
    1 |1236     |444   |No

I'm using sql server

It should be possible to change the CaseId value.. The CaseId value should be a parameter.. because the query need to be a part af stored procedure..

according to your criteria, shouldn't the result be every row with `CaseId = 1`?, or is it another requirement that there must be duplicates? — Lamak, Feb 24 '16 at 15:46
Possible duplicate of [How do I find duplicates across multiple columns?](http://stackoverflow.com/questions/8149210/how-do-i-find-duplicates-across-multiple-columns) — Tab Alleman, Feb 24 '16 at 15:52

Jesús López · Accepted Answer · 2016-02-24T16:04:21.437

3

The following query shows you those rows of YourTable whose CaseID is equals to the given parameter and that there is at least one row on the same table with the same CaseID, the same CodeNr, but different ProductNr. This is what you are looking for?

SELECT *
FROM YourTable t
WHERE EXISTS (
   SELECT *
   FROM YourTable i
   WHERE t.CaseID = i.CaseID AND t.ProductNr <> i.ProductNr AND t.CodeNr = i.CodeNr
) AND t.CaseID = @CaseID

edited Feb 24 '16 at 16:04

answered Feb 24 '16 at 15:54

Jesús López

8,338
7
40
66

Hi thanks.. but it should be possible to change the CaseId value.. The CaseId value should be a parameter.. because the query need to be a part af stored procedure.. – Balatharan Feb 24 '16 at 16:02
@Balatharan Edited to add `@CaseID` parameter – Jesús López Feb 24 '16 at 16:04
Hi Jesus, your query give me the desired result thanks a lot.. just a another question does the EXISTS give any problem, someone told me to not use EXISTS, i don't know quite way? – Balatharan Feb 24 '16 at 16:10
No problem with `EXISTS` at all. But if you want your query to be fast, add an index to the table on the columns: `CaseID` and `CodeNr`. `CREATE INDEX IX_YourTable_CaseID_CodeNr ON YourTable(CaseID, CodeNr) INCLUDE (ProductNr)` – Jesús López Feb 24 '16 at 16:16

Giorgos Betsos · Answer 2 · 2016-02-24T16:16:55.340

You can use:

SELECT CaseID, ProductNr, CodeNr, Test
FROM (
  SELECT CaseID, ProductNr, CodeNr, Test,
         COUNT(CodeNr) OVER (PARTITION BY CodeNr) AS cnt
  FROM mytable
  WHERE CaseID = 1) AS t
WHERE t.cnt > 1

COUNT window function is applied on CodeNr partitions. Any record that belongs to a partition with a population greater than 1 will be returned by this query.

Note: You can additionally use RANK in case of more than one groups of duplicate records, so as to discern between records that belong to separate CodeNr groups.

Note2: This query works as long as each CodeNr partition contains only unique ProductNr values.

My initial attempt was at the wrong path, had to completely rewrite my answer. Apologies. — Giorgos Betsos, Feb 24 '16 at 16:08

Finding duplicate rows based on value in different column

2 Answers2