Deleting Records in a Table

Hi,
I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
Thanks.
Example:
Table:
column 1 Column2 Column3 Column4
1 10 23 15
2 12 26 14
3 13 25 14
4 100 250 14
I want to delete records number 3-4 but retain the 2nd record.Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten|||Originally posted by CarstenK
Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten
Hi Carsten.
Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
Thanks.|||Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten|||Originally posted by CarstenK
Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten

Where and How do we Delete the repititive records from the Original Table?
Thanks Again!|||Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Thank you, Mr. Carsten!!!!!|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Hi Carsten,
I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete...it is not working! Any suggestions for this??|||Hi there,

the normal syntax for delete looks like

delete from <table_name>
[where <where_condition>

Carsten|||You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.

Here is my preferred method, using joins instead of where clause:

delete
from YourTable
inner join
(select column4, min(column1) column1
from YourTable
group by column4) FirstValues
on YourYable.column4 = FirstValues.column4
where YourTable.column1 > FirstValues.column1

WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.

blindman|||Hi, I used the owner moon:

create table moon.tempt
(tid integer primary key,
value integer)

insert into moon.tempt values( 1, 15)
insert into moon.tempt values( 2, 14)
insert into moon.tempt values( 3, 14)
insert into moon.tempt values( 4, 14)

delete t1
from moon.tempt t1
where
t1.tid <> (select min(t2.tid)
from moon.tempt t2
where t2.value = t1.value)

finally:

select * from moon.tempt

tid value
---- ----
1 15
2 14|||Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run

select min(t2.tid) from moon.tempt t2 where t2.value = t1.value

...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.

Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.

blindman