Find duplicates on multiple columns in a SQL

Question

I want to find the batchID who are having subjectID(0,1,2) only, pls help me, thanks in advance

I need answer BatchID=12 and BatchID=51, what can I do in sql

MyTable

uid BatchID SubjectID
6   2   0
7   2   1
8   2   2
9   2   4
10  3   0
11  3   1
12  4   5
13  4   0
14  5   5
15  6   5
17  7   0
18  7   1
19  7   2
26  12  0
27  12  1
28  12  2
29  1   0
30  1   1
31  1   4
62  45  5
63  46  0
64  46  1
65  46  4
107 49  6
108 49  2
109 49  4
110 50  1
111 50  3
116 0   1
117 0   4
118 51  0
119 51  1
120 51  2

please show your expected output,also include table creation statements to make it easy for others — TheGameiswar
– TheGameiswar, Commented Apr 4, 2017 at 15:53

Gurwinder Singh · Accepted Answer · 2017-04-04 15:53:43Z

1

You can use conditional aggregation for this:

select batchId
from your_table
group by batchId
having count(distinct case when subjectID in (0,1,2) then subjectID end) = 3
and count(case when subjectID not in (0,1,2) then 1 end) = 0

Explanation:

Group by batchId - Aggregate on batchId
count(distinct case when subjectID in (0,1,2) then subjectID end) - Produces 3 only if all three of them are present for this batchId
count(case when subjectID not in (0,1,2) then 1 end) - Produces 0 if there is no other subjectID except 0,1,2 assuming nulls are not allowed in the subjectId column.

answered Apr 4, 2017 at 15:53

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

1 Comment

jai Over a year ago

pls help me on these question - stackoverflow.com/questions/43368121/…

Kannan Kandasamy · Accepted Answer · 2017-04-04 16:03:58Z

0

You can use Row_Number()

;with cte as (
    select *, RowN = row_number() over (partition by batchid order by uid) from #yourbatch where subjectid in (0,1,2)
) select distinct BatchId from cte where RowN > 1

answered Apr 4, 2017 at 16:03

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

Find duplicates on multiple columns in a SQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related