quote:

---

Originally posted by balakathiresan

Thanks Peso

In ROW_NUMBER()function not working in sql server 2000. If any another idea tell me

Thanks
Balakathriesan

---

quote:

---

Originally posted by balakathiresan

Thanks vikash

I am not set primary key and uni key

empid emmpname occupation salary
1 bala prgr 5000
1 bala prgr 5000
1 bala prgr 5000
1 bala prgr 5000
1 bala prgr 5000
1 bala prgr 5000


Remove duplicate records
i need result as follows

empid emmpname occupation salary
1 bala prgr 5000

---

SELECT IDENTITY(int,1,1) AS ID,
empid, emmpname, occupation, salary
INTO #Temp
FROM YourTable
--for selection
SELECT empid, emmpname, occupation, salary
FROM
(
SELECT
(SELECT COUNT(*) FROM #Temp WHERE empid =t.empid
AND emmpname=t.emmpname
AND occupation=t.occupation
AND salary=t.salary
AND ID<=t.ID) AS Seq,empid, emmpname, occupation, salary
FROM #Temp t
)r
WHERE Seq>1

--for deletion
DELETE y
FROM YourTable y
LEFT JOIN (
SELECT
(SELECT COUNT(*) FROM #Temp WHERE empid =t.empid
AND emmpname=t.emmpname
AND occupation=t.occupation
AND salary=t.salary
AND ID<=t.ID) AS Seq,empid, emmpname, occupation, salary
FROM #Temp t
)r
ON r.Seq=1
AND r.empid=y.empid
AND r.emmpname=y.emmpname
AND r.occupation=y.occupation
AND r.salary=y.salary
WHERE r.empid IS NULL