sql query of duplicate records

[shahjapan]

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........whe re 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

[bhushanbagul]

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........whe re 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_bran ch
WHERE temp.branch_id = duplicated_bran ch.branch_id
AND temp.comp_id = duplicated_bran ch.comp_id
ORDER BY temp.comp_id, temp.branch_id;

Cheers
Bhushan

[shahjapan]

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_bran ch
WHERE temp.branch_id = duplicated_bran ch.branch_id
AND temp.comp_id = duplicated_bran ch.comp_id
ORDER BY temp.comp_id, temp.branch_id;

Cheers
Bhushan

thanks

I have checked,

it works

[bhushanbagul]

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

[shahjapan]

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

select * from temp t where ((select count(*) from temp where branch_id=t.branch_id and comp_id=t.comp_id)>1);