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

[santhanalakshmi]

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

[gpl]

add a unique index across all your columns

to get rid of the duplication, delete both rows and re-enter one of them

[NeoPa]

Use one of the SELECT predicates DISTINCT or DISTINCTROW.

If your query (SQL) started as :

SELECT *
FROM   [YourTable]

then this would be changed to :

SELECT DISTINCT *
FROM   [YourTable]

[santhanalakshmi]

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

[santhanalakshmi]

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

[gpl]

"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

[santhanalakshmi]

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

[gpl]

Of course its a waste, it should never have happened, your db designer should be shot

Firstly, pull out your duplicates

Select
rollno,
crseid,
semester,
period
From mytable
Group by 
rollno,
crseid,
semester,
period
Having Count(*) > 1

Now delete the duplicates and from the list you saved before, re-insert them
Now apply the unique index and it wont happen again

[NeoPa]

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.