I found some duplicate data as I was going thru the logic of a data pump. The entire row is not duplicated however.I would like to delete only the one row.
This is a sample of the data:
DECLARE @SomeData TABLE
(
FirstName varchar(25)
, MiddleName varchar(25)
, LastName varchar(25)
, StreetAddress varchar(25)
, Suite varchar(25)
, City varchar(25)
, [State] varchar(25)
, PostalCode varchar(10)
[code]...
As you can see, Joe Smith has two rows, but only one of the rows is complete. I would like to delete only the row that has a NULL value in the phone and area code for Joe Smith. There are a few thousand rows that are like this. They have duplicates all but the area code and phone number.I am used to using a CTE to remove duplicates, but I am a little lost on this one. The things that I have tried, have not worked exactly as I planned.
I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..
I am a newb at ms sql and was hoping someone could help me eliminate duplicate PRODUCT.PRODUCT from this statement. I have tried using DISTINCT with the same results.The ProductImage table is causing this because the duplicates are from the PRODUCT.PRODUCT that have more than 1 image.
If anyone could rewrite this statement so I can learn from this, it would be most appreciated!
I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with
ID Name Add1 Add2 1 Matt 16 Nowhere St Glasgow 1 Matt 16 Nowhere St Glasgow, Scotland 2 Jim 23 Blue St G65 TX 3 Bill 45 Red St 3 Bill 45 red St London
The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?
EDITION PRODUCT INSERTDATE ---------- ------------ ---------------------- CNE TN-Town News 12/19/2007 12:00:00 AM TN TN-Town News 12/19/2007 12:00:00 AM
What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.
Below is a raw data with SQL script to create the temp table. This table is a scale down version of what I am working with. I am taking the addresses from several different sources and putting them in a temp table. Now, I need to remove the duplicate addresses for each ID. Duplicate addresses are okay for different IDs.
Raw Data IDAddr1 CityStateZip 26205 N Main STChicagoIL52147 26205 N Main STChicagoIL52147 2685 Park AveChicagoIL52147 3535 Main StAustinTX78715 35976 Ponco StDallasTX79757 359587 MopacAustinTX78715 4558741 Len LnDaytonFL74717 455518 Spring DrDaytonFL74717 45585 Park AveChicagoIL52147
Desired Result IDAddr1 CityStateZip 26205 N Main STChicagoIL52147 2685 Park AveChicagoIL52147 3535 Main StAustinTX78715 35976 Ponco StDallasTX79757 359587 MopacAustinTX78715 4558741 Len LnDaytonFL74717 455518 Spring DrDaytonFL74717 45585 Park AveChicagoIL52147
Hi everyone.How can I get the unique row from a table which contains multiple rowsthat have exactly the same values.example:create table test (c1 as smallint,c2 as smallint,c3 as smallint )insert into test values (1,2,3)insert into test values (1,2,3)i want to remove whichever of the rows but I want to retain a singlerow.TIADiego
I've got the following table data:116525.99116520.14129965.03129960.12129967.00And I need to write a query to return only rows 2 and 4, since theremaining rows have duplicate IDs. I've tried the Group By, but amhaving no luck.Thanks!
I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates:
Code Snippet
DELETE FROM _email WHERE _email.eid IN ( SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists ( SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1 ) ) AND _email.eid NOT IN ( SELECT Min(eid) FROM _email AS tbl1 WHERE Exists ( SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1 ) GROUP BY emailaddress ); This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster?
I have a CSV file which contains some duplicate record and i have to load this file in SQL server database using SSIS package .
What i have to do is read the file and if the same record entry is occur more than 10 times for a particular unique combination ( like ID , Date , Time ) then i need to take only one record for that occurance.
I am working SQL Server 2005 and One Table Which contain only one column without primary keyNow I want to remove all duplicate value from that table with only single query
I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove... my tables is like that
I have a database being populated by hits to a program on a server.The problem is each client connection may require a few hits in a 1-2second time frame. This is resulting in multiple database entries -all exactly the same, except the event_id field, which isauto-numbered.I need a way to query the record w/out duplicates. That is, anyrecords exactly the same except event_id should only return one record.Is this possible??Thank you,Barry
There's some SQL below (T-SQL) & I'm wanting to have this result set grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate values. I only want to get rows for one instance of the Venue_ID (per BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks, Darren darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID, Booking_Header.Booking_Header_Description, Booking_Header.BStat_ID, Booking_Header.BT_ID, Booking_Header.Tagged, Booking_Header.Status_Timestamp, Booking_Header.Start_Date, Booking_Header.Days_Qty, Proposal.PPL_ID, Proposal.PPL_Status, Booking_Component.BCOM_ID, Booking_Component.Component_Description, Booking_Component.Venue_ID, Venue.Venue_Code, Venue.Description, Address.Address_ID, Address.Town, Booking_Status.BStat_Description, Booking_Type.Type_Description FROM dbo.Booking_Header INNER JOIN dbo.Proposal ON dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN dbo.Booking_Component ON dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER JOIN dbo.Venue ON dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN dbo.Address ON dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN dbo.Booking_Status ON dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER JOIN dbo.Booking_Type ON dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID WHERE (dbo.Proposal.PPL_Status = 1) AND (dbo.Booking_Header.BH_ID = 10)
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com 1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com 1 2 xyzabc NULL NULL 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL Now, I want unique rows from the above result set like :- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
I have a dataset in my report that pulls 10 fields. One of the tablixes on report should display only 8 of the 10 fields and this is causing the duplicate records to show up on the tablix.I tried hide duplicates option for the entire details row and have set the scope to "Details". It did not work. I am getting the data from a stored procedure and cannot do much on that.
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
I have a table with 2 columns, col1 is unique, col2 is not. col1 is numeric col2 is varchar. Here is the problem, col2 will have duplicate values, I need the largest numeric value from col1 with unique value from col2. Thanx for any help.
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
SELECT DISTINCT S.EnrollNo ,S.Name ,ET.Descriptions AS EventName ,SA.Name AS AttendStudent ,'' AS AttendFaculty FROM StudentEvent SE INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1) from trace_table where TextData like '%sp_%' and TextData like '%declare%'
I have a bunch of contacts that I've scored how well their names match to other contacts in the same business. I can programmatically figure out how to parse the results, but would like to know how to do this via SQL. My problem is for Business_fk 968976 I have 7 contacts. In the end I should have 4 contacts based on name match. For the business key listed Gerardo Lopez is in the ContactScore table twice for Contact keys 7355719 and 57028145. I then have two rows like so:
Each reference each other, and 2 is a good case, a more difficult case would have key 1 listed 10 times showing a ContactMatch_fk of 2 - 11, and then Contact_fk 2 listed 10 times with a ContactMatch_fk of 1, 3-11.I know 57028145 maps to 7355719 from the first row in the ContactScore table, so when Contact_fk of 7355719 comes up I should be able to skip it and not process that match. Hopefully that makes sense. Anyway here is the test data:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContactScore]') AND type in (N'U')) DROP TABLE [dbo].[ContactScore]; GO CREATE TABLE [dbo].[ContactScore] ( [ContactScore_pk]INT NOT NULL, [Contact_fk]INT NOT NULL,
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since. We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down. My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID. How are these duplicate records inserted with a simple insert statement & update ?? Is this a Locking problem or Transaction problem I have no idea. Any ideas will be of great help Thanks in Advance Stored Procedure Used
CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO
We have a data warehouse staging database in which we capture change history for hundreds of tables from a source system. In the source system, records are updated in place, but in our data warehouse we capture these changes by "terminating" the existing record and adding a new record reflecting the changes. In the data warehouse we add two columns to every table -- effective_date and expiration_date -- which indicate the dates the record was in effect in the source system. By convention, an expiration_date of 6/6/2079 means the record is currently still active in the source system. Each day we simply compare yesterday's version of the record (in the data warehouse) against today's version (in the source system). If differences are found in any of the columns, we terminate the record and add a new one, setting those dates appropriately.
In this example, the employee_id column is the natural key in the source system. We add the effective_date and expiration_date in the data warehouse, so those three columns together make up the key in the data warehouse. The employee_name, employee_dept, and last_login_date columns all come from the source system as well.
In the select output, you can follow the trail of changes for each of these three employees. Bob moved from dept 7 to 8 at some point; Frank didn't change departments at all; Cheryl moved from dept 6 to 9 and later back to 6. However, the last_login_date was updated frequently for all these employees.
We've tracked hundreds of tables this way for years, some with hundreds of columns. For optimization purposes, I'm now interested in trimming the fat a bit. That is, we track changes in many columns that we don't really need in our data warehouse. Some of these columns are rapidly-changing, causing all sorts of unnecessary terminate/inserts in the data warehouse. My goal is to remove these columns, reclaim the disk space and increase the ETL speed. So in this example, let's get rid of the last_login_date column.
alter table mytbl drop column last_login_date select * from mytbl order by employee_id, effective_date
Now in the select output, you can see we have many "effective duplicate" records. For example, nothing changed for Bob between 1/1/2014 and 1/31/2014 -- those really should be one record, not three. Here's the challenge: I'm looking for an efficient way to merge these "effective duplicates" together, through set-based sql updates/deletes/inserts (hoping to avoid any RBAR operations). Here's what the table ultimately should look like (cheating to get there):
Note that Bob only has two records (he changed department), Frank only has one record (no changes), and Cheryl has three records (two department changes).
My inclination would be to drop the unwanted columns, then GROUP BY all the remaining columns from the source system, and taking the MIN effective_date and MAX expiration_date. However, this doesn't work for cases like Cheryl's -- she moved to another department, then back again, so that change history needs to be retained.
As I mentioned, we have hundreds of tables, and I'd like to strip out dozens (maybe hundreds) of unused columns, so ultimately there will be millions of these pseudo-duplicates that need to be merged together. These are huge tables, so I really need to find an efficient set-based approach to this.
