473,567 Members | 2,980 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to avoid duplicate records in a table for a group by clause ?

147 New Member
Hi,
I am using SQL Server 2000. I am not using any Primary Keys in my table.

Table Fields:

rollno crseid semester period[/b]
s1 61820 01 JUL-NOV 2010
s1 84280 01 JUL-NOV 2010
s1 61800 01 JUL-NOV 2010
s1 61810 01 JUL-NOV 2010
s1 99830 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010



See the 6th and 7th record, you find the duplicate crseid(99790) for the same rollno and the period.

I want to avoid this. Please help me out.Thanks in advance
Oct 20 '10 #1
8 3510
gpl
152 New Member
add a unique index across all your columns

to get rid of the duplication, delete both rows and re-enter one of them
Oct 20 '10 #2
NeoPa
32,564 Recognized Expert Moderator MVP
Use one of the SELECT predicates DISTINCT or DISTINCTROW.

If your query (SQL) started as :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [YourTable]
then this would be changed to :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT *
  2. FROM   [YourTable]
Oct 20 '10 #3
santhanalakshmi
147 New Member
hi,

For SELECT Query Result, we can use DISTINCT or DISTINCTROW. But i dont need this. While doing data manipulation like INSERTING or UPDATING, i wants to stop the duplicate entry. I am not using any keys in my table.

My Table data looks like this,
Example:
rollno crseid semester period
s1 61820 01 JUL-NOV 2010
s1 84280 01 JUL-NOV 2010
s1 61800 01 JUL-NOV 2010
s1 61810 01 JUL-NOV 2010
s1 99830 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010

s2 61820 01 JUL-NOV 2010
s2 84280 01 JUL-NOV 2010
s2 61800 01 JUL-NOV 2010
s2 61810 01 JUL-NOV 2010
s2 99830 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010



see the rollno's s1 and s2, the crseid(99790) having double entry for the rollnos s1 and s2.Please help me out
Oct 21 '10 #4
santhanalakshmi
147 New Member
Hi,
How we can add, Unique index for all the columns.It won't allow more than one row to enter for the same rollno.

My Table data looks like this,
Example:
rollno crseid semester period
s1 61820 01 JUL-NOV 2010
s1 84280 01 JUL-NOV 2010
s1 61800 01 JUL-NOV 2010
s1 61810 01 JUL-NOV 2010
s1 99830 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s2 61820 01 JUL-NOV 2010
s2 84280 01 JUL-NOV 2010
s2 61800 01 JUL-NOV 2010
s2 61810 01 JUL-NOV 2010
s2 99830 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010


see the rollno's s1 and s2, the crseid(99790) having double entry for the rollnos s1 and s2.Please help me out
Oct 21 '10 #5
gpl
152 New Member
"How we can add, Unique index for all the columns.It won't allow more than one row to enter for the same rollno."

By deleting the duplicate entries first ... as the whole row is duplicated, you will need to delete both and re-insert just 1 copy of the rows you deleted
Oct 21 '10 #6
santhanalakshmi
147 New Member
Hi,

I know that method, what do you meant to say? But i don't want to do this. Inserting duplicate values and then manually deleting.Its waste. Whether there is an any way to do this using SQL ? Thanks
Oct 21 '10 #7
gpl
152 New Member
Of course its a waste, it should never have happened, your db designer should be shot

Firstly, pull out your duplicates
Expand|Select|Wrap|Line Numbers
  1. Select
  2. rollno,
  3. crseid,
  4. semester,
  5. period
  6. From mytable
  7. Group by 
  8. rollno,
  9. crseid,
  10. semester,
  11. period
  12. Having Count(*) > 1
  13.  
Now delete the duplicates and from the list you saved before, re-insert them
Now apply the unique index and it wont happen again
Oct 21 '10 #8
NeoPa
32,564 Recognized Expert Moderator MVP
SanthanaLakshmi :
Inserting duplicate values and then manually deleting.Its waste.
No one ever suggested this.

The suggestion was to delete the duplicate entries (that you've already allowed into your table) first.
When that has been done the server will allow you to set up an unique index across all your fields. This should be done next.

Please, when asking for help, try to do as instructed before turning round to criticise those who are spending their time helping you. If the advice isn't working for you, then the problem is not with the advice.
Oct 21 '10 #9

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

Similar topics

9
5976
by: atse | last post by:
Hi, My table in the database may contain duplicate records, which means except the primary key (auto_increment) field is unique, all or almost of all the fields are with the same content. How can I select them to display and delete them? Thanks for any idea. Atse
2
4077
by: Ross | last post by:
i have less hair now than i used to...i have a database with many duplicate records in it with only one exception, there is one field with the date it was put in the database and that is different. i can eliminate duplicate records with ease but how do you specify in an sql statement to delete the one with the earlier date? please help me...
2
28872
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to keep both records (or it could be more than 2 as well) where duplicate records are found. Also, I am interested in selecting all columns from the...
2
2055
by: nethravathy | last post by:
Hi, The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not. I tried with following query 1)SELECT elcbtripselect.ELCBTRIP_voltsMIN, elcbtripselect.ELCBTRIP_voltsMAX, elcbtripselect.ELCBTrip_is_partwinding, elcbtripselect.ELCBTrip_is_ydelta,...
5
2687
by: ramab | last post by:
Hi. There are two fields in my table that i check so that both of them doesnt occur together again to create a duplicate record. My problem is that if one of the field loses focus then i am able to store that record which i do not want. i cant put a duplicate restriction in the table. Please help. Thx. My code is as follows: Private Sub...
3
1762
by: pbrown | last post by:
Hi. I'm a relatively new Access 2000 user, and I've got a problem thats got me stumped. I have a table of street addresses and property numbers that looks something like this: Name, Location, PropertyNum Ralph, 100 Anywhere St, 400010008 Bob, 102 Anywhere St, 400010009 Jessica, 103 Anywhere St, 400010010 Joe, 103 Anywhere St, 400010010...
3
4036
by: audj | last post by:
Hello, I am trying to use a subquery to avoid duplicate entries when someone submits a form to subscribe to a mailing list. So I want to check if the email exists before adding the record. I tried something like: INSERT INTO mailing_list (ID,Email) VALUES ('','$Email') WHERE NOT EXISTS (SELECT * FROM mailing_list WHERE Email LIKE...
5
5432
by: mercea | last post by:
hi all, How do i avoid duplicate records on my database? i have 4 textboxes that collect user information and this information is saved in the database. when a user fills the textboxes and clicks the submit button, i want to check through the database if the exact records exist in the database before the data is saved. if the user is registered...
2
3540
Zwoker
by: Zwoker | last post by:
Greetings everyone, I have a problem that I hope has a simple solution. I am using MS Access 2003. I have a table that is a list of financial transactions. I am using a make table query over that table, and want to return a single record where multiple exist for the five fields that are keys on the table. The rule as to which record I want...
3
9591
by: smugcool | last post by:
HI, I am having a database in access. Where user imports various data which is in excel format. I want them to stop importing any duplicate record. If any duplicate record is been trying to import by user, access should generate an error files saying record already exists. Kindly suggest how to go ahead. Thanx in advance Anup
0
7688
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...
0
7590
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...
0
7905
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. ...
0
7960
tracyyun
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...
0
6272
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...
0
5216
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...
0
3630
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2093
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
1
1205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.