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
– Lamak, Commented Feb 24, 2016 at 15:46
Possible duplicate of How do I find duplicates across multiple columns? — Tab Alleman
– Tab Alleman, Commented Feb 24, 2016 at 15:52

Jesús López · Accepted Answer · 2016-02-24 16:04:21Z

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, 2016 at 16:04

answered Feb 24, 2016 at 15:54

Jesús López

9,3489 gold badges47 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Balatharan Over a year ago

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..

Jesús López Over a year ago

@Balatharan Edited to add @CaseID parameter

Balatharan Over a year ago

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?

Jesús López Over a year ago

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)

Giorgos Betsos · Accepted Answer · 2016-02-24 16:16:55Z

-1

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.

edited Feb 24, 2016 at 16:16

answered Feb 24, 2016 at 15:46

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

1 Comment

Giorgos Betsos Over a year ago

My initial attempt was at the wrong path, had to completely rewrite my answer. Apologies.

Collectives™ on Stack Overflow

Finding duplicate rows based on value in different column

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related