finding dups for id accross countrycode

Hi,
what is best way to find dups data looks like below:

id   cntry
123 ca
123 ca
123 us
456  ca
456 ca
657  mx
657  mx
657 us
657 us

i want results like
123 ca
123 us
657  mx
657 us
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
select id, cntry,  count(*)  from tablex
group by id, cntry
having count(*) > 1
order by id, cntry
0
Pawan KumarDatabase ExpertCommented:
Please use like below-

SELECT id   ,cntry
FROM yourtableName
GROUP BY id   ,cntry

or

SELECT DISTINCT id   ,cntry
FROM yourtableName
0
Pawan KumarDatabase ExpertCommented:
flow01's solution will not work as if you have a non duplicate row - that will not come in the output.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

flow01Commented:
Pawan is right,  your "find dups data" question does not fit your wanted result.
0
Pawan KumarDatabase ExpertCommented:
No the question is correct. We should just remove the having clause to get the desired results. Please refer my last comment.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this instead:

with cte as 
(
SELECT id, cntry, rank() over (partition by id order by cntry) rk
FROM Country
)
select distinct a.* from
Country a inner join cte b
on a.id = b.id
where b.rk > 1

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please try full tested and easy to understand solution.

Data Creation-
CREATE TABLE findFups
(
	 id   INT 
	,cntry VARCHAR(2)
)
GO

INSERT INTO findFups VALUES
(123,'ca'),
(123,'ca'),
(123,'us'),
(456,'ca'),
(456,'ca'),
(657,'mx'),
(657,'mx'),
(657,'us'),
(657,'us')
GO

Open in new window


Solution
SELECT id,cntry
FROM 
(
	SELECT * , COUNT(*) OVER (PARTITION BY id) rnk
			 , COUNT(*) OVER (PARTITION BY id,cntry) rnk1
	FROM findFups
)u WHERE rnk <> rnk1
GROUP BY id,cntry

Open in new window


Output
/*------------------------
OUTPUT
------------------------*/
id          cntry
----------- -----
123         ca
123         us
657         mx
657         us

(4 row(s) affected)

Open in new window

0
Mark GeerlingsDatabase AdministratorCommented:
Pawan: this may be SQL Server syntax (it certainly isn't Oracle syntax with "GO" on the last line):

CREATE TABLE findFups
(
       id   INT
      ,cntry VARCHAR(2)
)
GO

I think your first simple suggestion is best:

SELECT id   ,cntry
 FROM [yourtableName]
 GROUP BY id   ,cntry;

Why suggest a more-complex query when this simple query works as requested?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Why suggest a more-complex query when this simple query works as requested?
I think the output is not that straight forward by using a simple group by as 456 ca was not being selected. so, we need to build extra logic for extraction.
0
slightwv (䄆 Netminder) Commented:
>>Please try full tested and easy to understand solution.

SELECT * , COUNT(*) OVER (PARTITION BY id) rnk

Open in new window


This also isn't Oracle syntax.  It's a simple fix but please don't post it as "tested" unless you've actually tested in against Oracle.



@sam2929,

Can you clarify your actual requirements?

Given the title mentions dupes, I'm not seeing why "456 ca" should not be in your expected results.

If I only go by your results and not any of the text you are looking for records with at least one exact dupe across id and cntry and and at least one other cntry for the id for the same id.  That seems like a very strange requirement  to me.
0
Pawan KumarDatabase ExpertCommented:
Please find the tested solution for oracle.  For Oracle we just need to specify the column Names instead of *, everything else is same.

CREATE TABLE findFups
(
	 id   INTEGER
	,cntry VARCHAR2(2)
);


INSERT INTO findFups VALUES (123,'ca');
INSERT INTO findFups VALUES (123,'ca');
INSERT INTO findFups VALUES (123,'us');
INSERT INTO findFups VALUES (456,'ca');
INSERT INTO findFups VALUES (456,'ca');
INSERT INTO findFups VALUES (657,'mx');
INSERT INTO findFups VALUES (657,'mx');
INSERT INTO findFups VALUES (657,'us');
INSERT INTO findFups VALUES (657,'us');

Open in new window


Solution

SELECT id,cntry
FROM 
(
	SELECT id,cntry , COUNT(1) OVER (PARTITION BY id) rnk
			 , COUNT(1) OVER (PARTITION BY id,cntry) rnk1
	FROM findFups
)u WHERE rnk <> rnk1
GROUP BY id,cntry

Open in new window


OUTPUT

|  ID | CNTRY |
|-----|-------|
| 123 |    ca |
| 123 |    us |
| 657 |    mx |
| 657 |    us |

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.