Hi,
i am new to SQL Server. I want to write a query where in i want to delete duplicate rows from a table keeping the master copy.
If it is MySQL or Oracle we can write that using built in rownum or rowid. How to do that task in SQL Server 2005. Is there anything like rowid, rownum in SQL Server? If not suggest me a way to do that?
...aazad
not exactally , but u can make use of 'TOP' or row_number() function..
select top 1 from table 1 order by column1
u can use top intelligently to get top/bottom nth row... given u have somethin to orderby
|||Rownum is a psuedo column that generates a logical sequence number so it will change depending on the query execution plan, data etc. Rowid on the other hand is a physical identifier (at least in Oracle). So how are you using these in your queries? What is the purpose of using something like ROWID? You do have primary key or unique key constraints on your tables right! It will be easier to suggest the alternatives if we know your use cases.|||
Hi chandar,
There can be a senario where a table has no primary key and has data. Later when i want to make a column as primary key, i need to delete the duplicates, which i dont want to do it manually. so i shud write a query where i can delete duplicate rows keeping one copy of it. I worked with MySQL and in MySQL i can write a query as follows
delete from test where rowid in ( select rownum from test where rownum not in ( select min(rownum) from test group by all_columns having count(*) > 1 ) group by all_columns having count(*) > 1
The above code deletes the duplicates the master copy in MySQL. I am using that logical column rownum. How to do the same job in SQL Server 2005?
Regards..,
Aazad
|||
okk...lect us say u want to make column1 as ur primary key in table1 , so to find out the duplicate(or more) entries of this key , use the following query...
select column1 from table1
group by column1
having count(column1)>1
this will enlist all the entries for column1 which r repeating...
|||
Thank god .. you are using SQL Server 2005 use the following query
Example:
CREATE TABLE Table1
(
[Id] [int] NULL
)
go
INSERT INTO Table1 values(10);
INSERT INTO Table1 values(10);
INSERT INTO Table1 values(20);
INSERT INTO Table1 values(20);
go
With Test(rownum,ID)
as
(
Select Row_Number() OVER (ORDER BY ID), * From Table1
)
Delete From Test Where rownum in
(
Select A.rownum From Test A JOIN Test B On B.Id=A.ID and A.rownum >= B.rownum
Group bY A.rownum,A.ID Having Count(A.ID) <> 1
)
|||hiuse newid() function
good luck
