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
4 4036
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
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
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
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);
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Not Me |
last post by:
Hi,
Having a table with some duplicate ID's (different data tho), how can I
return the list but with only one record from each ID? Would this be using
the first() function and grouping?
...
|
by: Tom Mitchell |
last post by:
All:
I'm stumped on a query. How do I find duplicates in a table where one
of the duplicates has values is a certain field and the other doesn't.
For example, I have the following table:
...
|
by: ms |
last post by:
Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3...
|
by: Trey |
last post by:
How do I tell an update query to only search the records that are older then
the current record?
I need to figure out how to write a query that will take a record, look to
see if any record...
|
by: G Gerard |
last post by:
Hello
I am trying to update a table (TableB) using records
from a second table (TableA)
|
by: jmarr02s |
last post by:
I don't know what I am doing wrong I get duplicate records when I query
Here is my SQL query code:
SELECT Utilization_T.Facid, Utilization_T.Year, Utilization_T.Beds,
Utilization_T.LicBeds,...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: PerumalSamy |
last post by:
Hi
I am having table with more 13 lakhs records.
I am having duplicate records in it.
i need to remove that.
I wrote the following query
SELECT *
|
by: bcquadrant |
last post by:
Hi there
I am trying to make an update query that will update information in all occurances of duplicate records based on the First record for that duplicate set.
There are about 30,000 records...
|
by: colin-whitehead |
last post by:
I have 2 tables
tblReports primary key UPN, plus numeric fields Effort, Attain, etc
tblComments numeric primary key ID & textfield Text
In the Query I select each record from tblReports...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |