469,097 Members | 1,304 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

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

147 100+
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 3170
gpl
152 100+
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,159 Expert Mod 16PB
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 100+
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 100+
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 100+
"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 100+
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 100+
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,159 Expert Mod 16PB
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

Post your reply

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

Similar topics

2 posts views Thread by Carroll | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.