473,691 Members | 3,174 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete Duplicate Values

46 New Member
Hi All

I have table and it have around 90000 records.Its primary key is autonumber field and it has also have date column and name, then some other columns
Now i have problem with the table,as my table contains duplicate entries for a particular date.How can i delete the duplicate entries from the table for that particular column,Now i am doing manually with name column as it will be unique for that date.Can any one help me giving the query by which if i execute the query the duplicate values for that particular date should be deleted.When i searched google, i found a solution of copying the table and then set the unique column to primary key and write append query so that duplicate values will not be inserted.,I wish if i get a query by which i can delete the duplicate entries.The unique column in my table is name.Thanks in advance
May 27 '08 #1
4 2898
FishVal
2,653 Recognized Expert Specialist
Hi, ramdil.

Take a look at a similar thread Delete Similar Records
May 27 '08 #2
ramdil
46 New Member
Hi

I had went through that link, but its not helping me in getting my result.I will just explain my scenario.My table has three columns,ID which is autonumber,Name which is text and date which is of type date.Now my duplicate data will be like this
100 TestName 23/01/2007
101 TestName 23/01/2007
102 Name2 24/01/2007
103 Name3 23/01/2007 and so on

Now i want a select statment which will fetch me only first two records ie 100 and 101 as you can see that name and date col are same and so is duplicate.Pleas e help me in this as i am stuck on this.Thanks in advance


QUOTE=FishVal]Hi, ramdil.

Take a look at a similar thread Delete Similar Records[/quote]
May 27 '08 #3
FishVal
2,653 Recognized Expert Specialist
Hi, ramdil.

If
  • that is one-time action to cleanup the table
  • you mention to delete [Date] and [Text] duplicates leaving only those having maximal [Number] field
, then the simplest way is to create a query grouping records by [Date] and [Text] with aggregating function - Max([Number]), and use the query to make new table.
Then you may delete the old one and compact/repair db.

Regards,
Fish
May 27 '08 #4
ramdil
46 New Member
Hi

Thanks for the information.I think it is the best idea




Hi, ramdil.

If
  • that is one-time action to cleanup the table
  • you mention to delete [Date] and [Text] duplicates leaving only those having maximal [Number] field
, then the simplest way is to create a query grouping records by [Date] and [Text] with aggregating function - Max([Number]), and use the query to make new table.
Then you may delete the old one and compact/repair db.

Regards,
Fish
May 28 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
2414
by: Alexander Anderson | last post by:
I have a DELETE statement that deletes duplicate data from a table. It takes a long time to execute, so I thought I'd seek advice here. The structure 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 |
14
16247
by: sdowney717 | last post by:
Using the the NumId from TitleData, I would like to delete the corresponding row in Bookdata using pure SQL. I want it to delete all rows in bookdata where the Titledata.NumID is a match to bookdata.id The two tables are linked in that the NumId of table Titledata is identical to the Id of table bookdata. I can, using ADO, loop thru deleting one by one but I would like to do this in a pure SQL statement. Is this possible? Any help is...
6
16111
by: Karl Richards | last post by:
I am attempting to delete duplicate rows in a spreadsheet using the Excel object. Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been able to find anything on it. What I have so far is: Excel.Range range = oSheet.get_Range("A1", Type.Missing); range = range.get_End(Excel.XlDirection.xlDown); string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1,...
1
7799
by: st12iker | last post by:
I managed to write a file to a hash. However the file contains multiple keys and value in the format listed. Note how the values are sorted in descending order. i.e. of file written to hash ASDF 5.908 ASDF 5.902 ASDF 5.123 QWER 3.098 QWER 3.023
7
2798
by: cobolman | last post by:
I have a table with the following columns, NAME, TYPE, TAG And there may be 'duplicates' on name and type. How can I delete them?? I want to delete all with duplicate NAME and TYPE
3
10943
by: rajeshkrsingh | last post by:
Hi friends, Step1- create table duplicate ( intId int, varName varchar(50) ) insert into duplicate(intId,varName) values(1,'rajesh') insert into duplicate(intId,varName) values(2,'raj12') insert into duplicate(intId,varName) values(1,'rajesh')
7
3900
by: jmstur2 | last post by:
I have a table with what I consider duplicate records. Data in all columns are duplicate except for the date column, meaning that duplicate data was entered on different dates and those dates were stored along with the data. I want to delete the records except for most recent one. I can select the client ID field and the max(date field) to determine the ones I want to keep, but how do I determine the ones to delete. There are often more than...
2
8707
by: sonia.sardana | last post by:
Create table info(comp_id int identity(101,1),comp_name varchar(50)) insert into info values('Progressive Ltd.') insert into info values('Progressive Ltd.') insert into info values('EliResearch') insert into info values('Patni') insert into info values('Accenture') insert into info values('Accenture') select * from info I want to delete those entries from the table that have duplicate company names.
1
4093
watertraveller
by: watertraveller | last post by:
Hi all. My ultimate goal is to return two columns, where no single value appears anywhere twice. This means that not only do I want to check that nothing from column A appears in column B and vice-versa, but I also don't want the same value appearing twice in A and twice in B. So far I have: --Diff the columns INSERT INTO @Table SELECT One, Two FROM @Column1 a FULL OUTER JOIN @Column2 b
0
8598
marktang
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...
0
9077
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8786
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7623
agi2029
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...
1
6457
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5811
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();...
0
4322
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...
1
2965
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
3
1952
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.