By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,315 Members | 2,150 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,315 IT Pros & Developers. It's quick & easy.

sql query of duplicate records

shahjapan
P: 63
create table temp(comp_id int,branch_id int);
insert into temp values (1000,1);
insert into temp values (1000,2);
insert into temp values (1000,3);
insert into temp values (1000,1);
insert into temp values (1000,2);
insert into temp values (2000,1);
insert into temp values (2000,2);
insert into temp values (2000,3);
insert into temp values (2000,1);


comp_id | branch_id
---------+-----------
1000 | 1
1000 | 1
1000 | 2
1000 | 2
1000 | 3
2000 | 1
2000 | 1
2000 | 2
2000 | 3

(9 rows)


I want only follwing rows........where branch_id is duplicated for any comp_id


comp_id | branch_id
---------+-----------
1000 | 1
1000 | 1
1000 | 2
1000 | 2
2000 | 1
2000 | 1
(6 rows)

send me the query for it
Jun 11 '07 #1
Share this Question
Share on Google+
4 Replies


P: 16
create table temp(comp_id int,branch_id int);
insert into temp values (1000,1);
insert into temp values (1000,2);
insert into temp values (1000,3);
insert into temp values (1000,1);
insert into temp values (1000,2);
insert into temp values (2000,1);
insert into temp values (2000,2);
insert into temp values (2000,3);
insert into temp values (2000,1);


comp_id | branch_id
---------+-----------
1000 | 1
1000 | 1
1000 | 2
1000 | 2
1000 | 3
2000 | 1
2000 | 1
2000 | 2
2000 | 3

(9 rows)


I want only follwing rows........where branch_id is duplicated for any comp_id


comp_id | branch_id
---------+-----------
1000 | 1
1000 | 1
1000 | 2
1000 | 2
2000 | 1
2000 | 1
(6 rows)

send me the query for it
Dear shahjapan

Please execute the below query, it'll give you the desired result.

SELECT temp.comp_id, temp.branch_id
FROM temp,
(SELECT branch_id, comp_id, COUNT (comp_id)
FROM temp
GROUP BY branch_id, comp_id
HAVING COUNT (comp_id) = 2) duplicated_branch
WHERE temp.branch_id = duplicated_branch.branch_id
AND temp.comp_id = duplicated_branch.comp_id
ORDER BY temp.comp_id, temp.branch_id;

Cheers
Bhushan
Jun 11 '07 #2

shahjapan
P: 63
Dear shahjapan

Please execute the below query, it'll give you the desired result.

SELECT temp.comp_id, temp.branch_id
FROM temp,
(SELECT branch_id, comp_id, COUNT (comp_id)
FROM temp
GROUP BY branch_id, comp_id
HAVING COUNT (comp_id) = 2) duplicated_branch
WHERE temp.branch_id = duplicated_branch.branch_id
AND temp.comp_id = duplicated_branch.comp_id
ORDER BY temp.comp_id, temp.branch_id;

Cheers
Bhushan
thanks

I have checked,

it works
Jun 12 '07 #3

P: 16
thanks

I have checked,

it works
Hi shahjapan

Please check it also the second way to achieve the same result.

SELECT a.comp_id, a.branch_id
FROM temp a, temp b
WHERE a.ROWID <> b.ROWID
AND a.comp_id || a.branch_id = b.comp_id || b.branch_id
ORDER BY a.comp_id, a.branch_id;

Cheers
Bhushan
Jun 12 '07 #4

shahjapan
P: 63
Hi shahjapan

Please check it also the second way to achieve the same result.

SELECT a.comp_id, a.branch_id
FROM temp a, temp b
WHERE a.ROWID <> b.ROWID
AND a.comp_id || a.branch_id = b.comp_id || b.branch_id
ORDER BY a.comp_id, a.branch_id;

Cheers
Bhushan
Another and easy one may be

Expand|Select|Wrap|Line Numbers
  1. select * from temp t where ((select count(*) from temp where branch_id=t.branch_id and comp_id=t.comp_id)>1);
Jun 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.