I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name
FROM tblTable
GROUP BY Name
ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...
Hi, I have table which stores the fund name and its data. We get quarterly information from the fund co. Suppose if the user wants to add a fund thats not in our database we let then add a ClientFundId and a FundName. But may be after sometime the fund company may add that fund in the next quarter.. So how do i get rid of Duplicated Data.. In the ClientFundId column we can a 9 letter Aplhanumeric or a 5 letter character but if the fund co.. provides those values the 5 letter characters are stored in Ticker column and the 9 letter words are stored in Cusip column.. So i just wrote this query hoping i could retrieve the duplicate values but it didnt list any..but i found one this is my query.. Select FundId, Cusip, Ticker, ClientFundId, FundName, ShortName From Fund Where
ClientFundId = Ticker or ClientFundId = Cusip Any help will appreciated Thanks Karen
does someone have a querry to display the duplicate records in a table.
Table: zipcode dma
My data upload is failing because there is a primary key on zipcode and the source data (42k records) has about 50 duplicate zipcode records in it. It is possible that there is a unique combo of zipcode / dma but I need to identify the duplicate records to determine that.
I have this script bellow which does what it is supposed to. However it only outputs the cust_id. I want it to show all the columns in the table. How would I do this?
SELECT cust_id FROM cust_table WHERE cust_name in ('Billy','John') and rownum < 100 GROUP BY cust_id HAVING COUNT(*) > 1;
Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.
my PERSON table is this below:
ID name fname ownerid id2
1 a b 2 c c 3 e f 4 a b 1 10 5 c c 2 11
I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname
select * from ( Select name ,fname, count(1) as cnt from PERSON group by name,Fname ) where cnt > 1
ID name fname ownerid id2
1 a b 4 a b 1 10
2 c c 5 c c 2 11
With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..
Serial Count 001 2 the count is 2 because Serial 001 has an MSDSID of 20 and 22 002 1 the count is 1 because Serial 002 only has MSDSID 21 003 2 the count is 2 because Serial 003 has an MSDSID of 21 and 22 004 1 the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
I have this query below that I created to do a count, but I don't think this is what I needed.
I need to find the duplicates. Example, if
CLI_ID1 12345 has 4 CLIP records, each CLIP record should have a different CLIP rank. I need to find scenarios where 2 (or more) of the CLIP records have the same CLIP RANK. If there are duplicate CLIP_RANKs within the same CLI_ID,
Select Distinct cli_id1, count(clip_rank) countrank FROM impact.dbo.CLI LEFT JOIN impact.dbo.CLIO ON CLI.CLI_ID1 = CLIO.clio_id1
left join impact.dbo.clip ON cli_id1 = clip_id1 Where (clio_trm = '' or clio_trm = NULL or clio_trm is null) group by cli_id1 order by cli_id1
Every sunday, new data will be loaded from temptable to main table. I have to make sure that, duplicates does not get loaded from temptable to maintable.
For example, if last sunday a record gets loaded from temp to main. If this sunday also the same record is present then it means that is a duplicate.
The duplicate is decided on below scenario
select 'CodeChanges: ', count(*) from CodeChanges a, CodeChanges_Temp b where a.AccountNumber = b.AccountNumber and a.HexaNumber = b.HexaNumber and a.HexaEffDate = b.HexaEffDate and a.HexaId = b.HexaId and
[Code] ...
Yesterday (Sunday) , data from temp got loaded onto maintable but with duplicates.
There is a log which just displays number of duplicates.
Yesterday the log displayed 8 duplicates found. I need to find out the 8 duplicates which got loaded yesterday and delete it off from main table.
There is a column in both tables which is 'creation date and time'. Every Sunday when the load happens this column will have that day's date .
Now i need to find out what are all the duplicates which got loaded on this sunday.
The total rows in temp table is : 363 No of duplicates present is : 8
I used below query to find out the duplicates but it is returning all the 363 rows from the maintable instead of the 8 duplicates.
Select 'CodeChanges: ', * from CodeChanges a where exists ( Select 1 from CodeChanges_Temp b where a.HexaNumber = b.HexaNumber and a.HexaEffDate = b.HexaEffDate and
[Code] ...
Need finding the duplicate records which has creation date time as '2015-11-01 00:00:00.000' and all the above columns mentioned in the query matches.
My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names Patient_Info: PatientID, table contains patient ID and other information Patient_PersonRelation: Person_ID, patientID, RelationType Address: Contains address of all person and patient (key PersonID) Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
I have a Table with records and some of them are actually multiple records. I need to query SQL 2005 (compare) field values on more than one column to return these records.
I am an absolute beginner and need some guidance on how to start on this. Can anyone advise.
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness]; GO CREATE TABLE [dbo].[TestBusiness]( [Business_pk] INT IDENTITY(1,1) NOT NULL, [BusinessName] VARCHAR (200) NOT NULL, [Address] VARCHAR(MAX) NOT NULL,
mytable fld1 int primkey fld2 varchar(20), fld3 varchar(20)From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2. I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates. select fld1, fld2, fld3 from mytable
I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 LEFT OUTER JOIN #BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0) As Actual_CurrentMonth,
Obviously, I'm new here so I'd like to start by thanking anyone for their help with this very newbie SQL search query question.
I have been tasked with trying to figure out how to get search results from several different columns of two different tables within a database. I have successfully accomplished this, but one column has many many duplicates. I have read on these forums about using the DISTINCT command to eliminate this, but so far have been unsuccessful. Here is the search I'm using...(columns 1, 2, and 3 are from one table and columns 4 and 5 are from another. The results need to be in ascending order (alphabetical) of column1.
select column1, column2, column3, column4, column5 from table1, table2 order by column1;
The result of this is great except that column1 has many duplicates.
I tried entering the search like this...
select distinct column1, column2, column3, column4, column5 from table1, table2 order by column1;
I have a DELETE statement that deletes duplicate data from a table. Ittakes a long time to execute, so I thought I'd seek advice here. Thestructure of the table is little funny. The following is NOT the table,but the representation of the data in the table:+-----------+| a | b |+-----+-----+| 123 | 234 || 345 | 456 || 123 | 123 |+-----+-----+As you can see, the data is tabular. This is how it is stored in the table:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 || 3 | a | 123 || 3 | b | 234 |+-----+-----------+------------+What I need is to delete all records having the same "Row" when there existsthe same set of records with a different (smaller, to be precise) "Row".Using the example above, what I need to get is:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 |+-----+-----------+------------+A slow way of doing this seem to be:DELETE FROM XWHERE Row IN(SELECT DISTINCT Row FROM X x1WHERE EXISTS(SELECT * FROM X x2WHERE x2.Row < x1.RowAND NOT EXISTS(SELECT * FROM X x3WHERE x3.Row = x2.RowAND x3.FieldName = x2.FieldNameAND x3.FieldValue <> x1.FieldValue)))Can this be done faster, better, and cheaper?
-- declared variables declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100) declare @list varchar(8000) -- set values to variables set @list = '' set @database_name = 'data200802_dan' set @table_name = 'other02' set @primary_key_field = 'callid'
use database
select @list = @list + column_name + ', ' from information_schema.columns where table_name = @table_name --table name and column_name != @primary_key_field --unique identifier select @list = substring(@list, 1, len(rtrim(@list)) - 1)
--above 5 lines btw came from a helper in the msdn forum. thanks
SELECT DISTINCT @list INTO '#' + @table_name FROM @table_name @table_name + ':' IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0 BEGIN INSERT INTO @database_name + '.dbo.' + @table_name + '(' + @list + ')' SELECT @list FROM '#' + @table_name END ELSE BEGIN DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')' GOTO @table_name END DROP TABLE '#' + @table_name
the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table.
is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query..
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1 IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2 IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
I am working with a bunch of records that have duplicates on the Persid and the intPercentID where there are duplicates I want to remove when I stick them in the temp table, I tried join on tempo table and doing not exists but still inserts, so now I am trying a merge but same thing. how can I keep duplicates from being inserted in the temp table. I made a cursor as well but its slow as heck, but it does work. trying better ways.
Create table #TempStr (STRId int not null Identity(1,1) primary key, Persid int, percentId int, dtCreated datetime, CreatedBy int)
INSERT #TempStr (Persid, percentId, dtCreated, CreatedBy) select intPersonnelID, intPercentID, dtSubmitted, intSubmittedBy from tblSTR whereintpercentId in (61,62) group by intPercentID, intPersonnelID, dtSubmitted, intSubmittedBy UNION ALL
We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
I have a table with several foreign key relationships. I am scripting with PHP and was wondering if anyone knows how to query the database to show the relationships that a table has. I have been using MySQL, but for a number of projects have to use MS SQL.
Data: insert into product_stocks values(1,'A',30) insert into product_stocks values(2,'A',70) insert into product_stocks values(3,'A',60) insert into product_stocks values(4,'A',40) insert into product_stocks values(1,'B',90) insert into product_stocks values(2,'B',60) insert into product_stocks values(3,'B',70) insert into product_stocks values(4,'B',40) insert into product_stocks values(1,'C',40) insert into product_stocks values(2,'C',50) insert into product_stocks values(3,'C',80) insert into product_stocks values(4,'C',90)
Result Set: product_type product_id no_of_units --------------- ------------- -------------- A 2 70 A 3 60 A 4 40 B 1 90 B 3 70 B 2 60 C 4 90 C 3 80 C 2 50
i.e The result set gives the top 3 products in each product_type based on the no_of_units.
I have a warehouse table but I don't know which query will update warehouse inside of information ? Thus, how to write a query list all query have include this warehouse table name in there ?
