472,363 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,363 software developers and data experts.

Delete Duplicate Values

46
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 2652
FishVal
2,653 Expert 2GB
Hi, ramdil.

Take a look at a similar thread Delete Similar Records
May 27 '08 #2
ramdil
46
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.Please 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 Expert 2GB
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
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
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...
14
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...
6
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...
1
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 ...
7
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
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')...
7
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...
2
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')...
1
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...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
0
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...

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.