A sql query to get all the records where 5 columns are same but only one column is different

Question

I have this table:

A   B   C   D
1  Cat XYZ 12
1  Cat XYZ 13
1  Dog XYZ 14
2  Dog ABC 15
2  Dog ABC 16
3  Cat XYZ 17

Result set:

A   B   C   D
1  Cat XYZ 12
1  Cat XYZ 13
2  Dog ABC 15
2  Dog ABC 16

I need all such records in my table where A, B, C should be same and the D column can vary. Please help it soon that's why asking for help.

score 3 · Answer 1 · answered May 18 '18 at 17:05

3

You can use exists:

select t.* 
from table t
where exists (select 1
              from table t1
              where t1.a = t.a and t1.b = t.b and t1.c = t.c and t1.d <> t.d
              );

answered May 18 '18 at 17:05

Yogesh Sharma

49,870
5
26
52

@Jini You state D *can* vary – paparazzo May 18 '18 at 17:39
Hi Yogesh, won't this ignore rows where all 4 columns (a,b,c,d) are the same. OP said D *can* vary, not sure if it must vary for it to be present in the results. – Rajesh Chamarthi May 18 '18 at 17:42
@Jini Mabe is worked on the posted data but on you stated requirement. If you had two 3 Cat XYZ 17 would you want the reported or not? – paparazzo May 18 '18 at 18:10
@paparazzo No, I wouldn't want that as it is redundant data and this query didn't return the records only occurring once. I got the expected results. – Jini May 24 '18 at 16:43
That query would NOT report two 3 Cat XYZ 17. – paparazzo May 24 '18 at 17:03

score 0 · Answer 2 · answered May 18 '18 at 17:07

0

Given that you want one column to be different, you would need to decide on what value of Column D you want to be chosen.

SELECT A, B, C, MAX(D) D
FROM table
GROUP BY A, B, C

In the above solution, I went the the maximum value of column D.

answered May 18 '18 at 17:07

Tolu

175
4
10

1

This wouldn't give the same result as they are expecting – Kevin Mee May 18 '18 at 17:09
OP stated that the one column (*D*) is different. So, although the sample data seems to identify same value for column D, grouping all columns would conflict with the question. – Tolu May 18 '18 at 17:11
if you group by all the columns then they will result it two separate rows in the result because column D is different – Kevin Mee May 18 '18 at 17:15
correct. Looks like we differ on the interpretation of the question. my understanding is that the data set contains rows that are identical when from columns **A**, through **C**, but different for column**D**. – Tolu May 18 '18 at 17:20
Very true -- rereading the question right now looks like they dont even have all potential groupings in their results – Kevin Mee May 18 '18 at 17:22

score 0 · Answer 3 · answered May 18 '18 at 17:16

If you just want the a, b, c values, you can use aggregation:

select a, b, c, min(d), max(d)
from t
group by a, b, c
having min(d) <> max(d);

If you want the actual rows, then Yogesh's solution with exists is probably the best approach, assuming none of the column values are NULL.

score 0 · Answer 4 · answered May 18 '18 at 17:41

0

This should do it.

select * 
from ( select a, b, c, d, 
       count() over (partition by a, b, c) cnt
       from t1
     ) t 
where t.cnt > 1

answered May 18 '18 at 17:41

paparazzo

44,497
23
105
176

score -1 · Answer 5 · answered May 18 '18 at 17:00

-1

Should be able to do this with a simple group by

SELECT A, B, C, D
FROM table
GROUP BY A, B, C, D

answered May 18 '18 at 17:00

Kevin Mee

539
3
14

1

Does not produce the desired results – paparazzo May 18 '18 at 17:23
I'm not sure OPs result set is complete -- where is the `A = 3` record? – Kevin Mee May 18 '18 at 17:27
A = 3 does not meet the criteria. Your answer does not produce the desired results. – paparazzo May 18 '18 at 17:31
based on this description it does -- `I need all such records in my table where A, B, C should be same and the D column can vary` – Kevin Mee May 18 '18 at 17:32
It is clear enough to me. There is no other record with A, B, C the same. For the 3rd time your query does not produce the desired results. – paparazzo May 18 '18 at 17:35

A sql query to get all the records where 5 columns are same but only one column is different

5 Answers5