CODEWITHSUNDEEP
sqloracle-database
user93865
user93865

Reputation: 139

Query to find duplicate values for two fields

Sorry for the Title, But didn't know how to explain. I have a table that have 2 fields A and B. I want find all rows in the table that have duplicate A (more than one record) but at the same time A will consider as a duplicate only if B is different in both rows. Example: 

   FIELD A        Field B
   10             10
   10             10 // This is not duplicate

   10             10
   10             5 // this is a duplicate

How to to this in a single query

Upvotes: 0

Views: 67

Answers (2)

ayman salah
ayman salah

Reputation: 73

select FIELD from your_table group by FIELD having count(b) > 1

take in consideration that this will return count of all duplicate example if you have values 1 1 2 1 it will return 3 for value 1 not 2

Upvotes: -1

Ben
Ben

Reputation: 52863

Let's break this down into how you would go about constructing such a query. You don't make it clear whether you're looking for all values of A or all rows but let's assume all values of A initially.

The first step therefore is to create a list of all values of A. This can be done two ways, DISTINCT or GROUP BY. I'm going to use GROUP BY because of what else you want to do:

select a
  from your_table
 group by a

This returns a single column that is unique on A. Now, how can you change this to give you the unique values? The most obvious thing to use is the HAVING clause, which allows you to restrict on aggregated values. For instance the following will give you all values of A which only appear once in the table

select a
  from your_table
 group by a
having count(*) = 1

That is the count of all values of A inside the group is 1. You don't want this of course, you want to do this with the column B. You need there to exist more than one value of B in order for the situation you want to identify to be possible (if there's only one value of B then it's impossible). This gets us to

select a
  from your_table
 group by a
having count(b) > 1

This still isn't enough as you want two different values of B. The above just counts the number of records with the column B. Inside an aggregate function you use the DISTINCT keyword to determine unique values; bringing us to:

select a
  from your_table
 group by a
having count(distinct b) > 1

To transcribe this into English this means select all unique values of A from YOUR_TABLE that have more than one values of B in the group.

You can use this method, or something similar, to build up your own queries as you create them. Determine what you want to achieve and slowly build up to it.

Upvotes: 3

Related Questions