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
4 4057
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
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
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?
Also, how would I choose which would be the first to be picked? If I did an
order by xx, would this alter the results, or is the first record picked
before the ordering is done?
|
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:
tblItems
ItemID
ItemLabel
|
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 fields (vehicleID, BattID, and ChgHrs). VehicleID and
BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since
records to be imported can have duplicate records of the composite key I need to
clean all but one of the...
|
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 with an older date and time has the same address, then
marks a yes/no box to YES if it does not find a duplicate.
I know this can be done, I just don't know how.
I know it will take a subquery of some kind. I am just lost. Any help would
|
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,
Utilization_T.NursingCare, Utilization_T.GenLicBeds, FormatDate(Now())
AS Expr1, Staffing_T.Username
FROM Staffing_T INNER JOIN Utilization_T ON Staffing_T.facid =
Utilization_T.Facid
|
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
renaming the duplicate records? My thinking was to take the results of the
duplicate query, and somehow have it number each line where there is a
duplicate (tried a groups query, but "count" won't work), then do an update
query to change the duplicate to...
|
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 in the table, and of them without the duplicates there is only about 4000 records.The duplicates are essential to work we do, so we cannot delete them.
I have a make table query that selects only the first record for a duplicate set based on the...
|
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 using UPN and for
each of the numeric fields use DLookup to look up the Text in
tblComments.
the expression DLookUp("Text","tblComments","ID=" & tblReports!Effort)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |