SQL to find rows where two columns have the same value

Question

I have 3 columns in Oracle database having table mytable and i want records having only duplicate values in 2nd and 3rd column.

SQL> select * from mytable ;

column1      column2       column3

  A            50           50----required output
  A            10           20----have different values i.e. 10 and 20
  A            50           50----required output
  A            30           70----have different values i.e. 30 and 70
  B            20           20----required output
  B            40           30----have different values i.e. 40 and 30

I want the following output with count(*):

 column1      column2       column3

  A            50           50
  A            50           50
  B            20           20

Any help is much appreciated

Primary key is unique from its definition. Here column1 isn't unique is it mistake in example data? — Michał Powaga
– Michał Powaga, Commented Apr 3, 2012 at 12:29

user unknown · Accepted Answer · 2012-04-03 12:41:30Z

5

select column1, count (*)
from mytable
where column2 = column3
group by column1, column2;

edited Apr 3, 2012 at 12:41

answered Apr 3, 2012 at 12:36

user unknown

36.4k12 gold badges77 silver badges123 bronze badges

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

Comments

harry · Accepted Answer · 2012-04-03 12:35:26Z

0

From your question it is not clear about primary key as A in First Column is being repeated many times.

You can try the following:

 select column1, column2, column3, count(*) from 
mytable where column2 = column3 group by column1, column2, column3;

answered Apr 3, 2012 at 12:35

harry

73111 silver badges24 bronze badges

Comments

Jayesh Sorathia · Accepted Answer · 2012-04-03 12:41:43Z

Here are sample example , i am doing this SQL Server but i am sure this query work in ORACLE also EXAMPLE :

Create table #Test (colA int not null, colB int not null, colC int not null, id int not null identity) on [Primary]
GO
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)

INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)

INSERT INTO #Test (colA,colB,colC) VALUES (4,5,6)
GO
Select * from #Test
GO

select count(colA) as tot_duplicate_count , colA ,colB ,colC  from #Test where id <=
(Select Max(id) from #Test t where #Test.colA = t.colA and
#Test.colB = t.colB and
#Test.colC = t.colC)
group by colA ,colB ,colC 
having count(colA) > 1

This query this total count of duplicate record per data row

Collectives™ on Stack Overflow

SQL to find rows where two columns have the same value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related