We help IT Professionals succeed at work.

sql duplicate rows from concatenated columns

471 Views
Last Modified: 2014-12-04
I have a table with 2 columns, code and country. Code is 9 characters. I would like to find all the rows in the table where the first 8 char plus the country are duplicates.
e.g.
123456781  AG
123456782  AG
123456781  BB
987654321  JM

would have concatentated values
12345678AG
12345678AG
12345678BB
98765432JM


which has the duplicate value 12345678AG
so I want to get only the records where the concatenated values are duplicated.
i.e.

123456781  AG
123456782  AG

I have used concatenation only to describe the problem.
I am fine if the solution can select these same record without concatenation.
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Give this a whirl..
SELECT LEFT(code, 8), country, COUNT(Country) as the_count
FROM YourTable
GROUP BY LEFT(code, 8), country
HAVING COUNT(Country) > 1

Open in new window

If you'd like some more reading on GROUP BY I have an image and code-heavy tutorial out there called SQL Server GROUP BY Solutions
CERTIFIED EXPERT

Commented:
More accurate solution could be this one:
SELECT * FROM YourTable
 WHERE LEFT(code, 8)+country IN (
      SELECT LEFT(code, 8)+country
        FROM YourTable
       GROUP BY LEFT(code, 8)+country
      HAVING COUNT(*) > 1)

Open in new window


MS SQL Server is OK with it. DB2? Who knows?
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I think DB2 does not use the left function but you should be able to use the substr function in its stead.
select substr(code,1,8), country, count(*)
from yourtable
group by substr(code,1,8), country
having count(*) > 1
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I missed where youi wanted to retrieve the complete code column
select y.code, y.country
from yourtable y,
(select substr(code,1,8), country, count(*)
 from yourtable
 group by substr(code,1,8), country
 having count(*) > 1) x
where x.country = y.country
and x.code = substr(y.code,1,8);

Author

Commented:
Perhaps I wasn't clear. The issue is to find the rows with duplicates of the combined columns LEFT(code,8) and country.
i.e. given
123456781  AG
123456782  AG
123456781  BB
987654321  JM
987654321  BB
987654322  BB

now adding a column for the first 8 digits of the code gives.

12345678  123456781  AG
12345678  123456782  AG
12345678  123456781  BB
98765432  987654321  JM
98765432  987654321  BB
98765432  987654322  BB

Wherever the combined value of the 8 digit code plus the country are duplicated the row  from the original table should be provided.

So what I want is

123456781 AG
123456782 AG
987654321 BB
987654322 BB

Note that rows 123456781  BB, 987654321  BB and 987654321  JM are omitted since the first 8 digits of the code combined with the country code are unique.

Author

Commented:
Oops that final note should have been

Note that rows 123456781  BB and 987654321  JM are omitted since the first 8 digits of the code combined with the country code are unique.
Information Technology Specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
What query provided this result:
12345678  123456781  AG
12345678  123456782  AG
12345678  123456781  BB
98765432  987654321  JM
98765432  987654321  BB
98765432  987654322  BB
Both my and awking00's code should work without problems on MS SQL.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Actually, my code won't work on MS SQL as is, but could be easily modified to do so by using the substring function (i.e. not substr which is DB2) and adding the AS keyword for the alias of the table and subquery.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.