;WITH [CTE DUPLICATE] AS 
(
SELECT 
  RN = ROW_NUMBER() OVER (PARTITION BY driverName ORDER BY driverName DESC),
  driverName 
FROM tblDriver
)
DELETE FROM [CTE DUPLICATE] WHERE RN > 1

quote:

---

Originally posted by namman

Also, is it possible to delete all instances except for one?


yes

1- create a temp table variable with the same structure as your table.
2- insert duplicated rows into temp table.
3- delete duplicated rows from your table.
4- insert duplicated rows into your table from temp table.


run example below to see if it works as you want.


declare @temp table(id int, name varchar(50))

--- example table -----
declare @yourTable table(id int, name varchar(50))
insert into @yourTable values(1, 'bob smith')
insert into @yourTable values(12, 'jane doe')
insert into @yourTable values(12, 'bob smith')
insert into @yourTable values(11, 'john deer')
insert into @yourTable values(10, 'jane doe')
insert into @yourTable values(2, 'jane doe')
insert into @yourTable values(3, 'eric brown')
insert into @yourTable values(23, 'jane doe')
-- original values -------
select * from @yourTable order by name

;with temp as
(
select *, ROW_NUMBER() over(PARTITION by name order by name) as rowid
from @yourTable
)
insert into @temp select id, name from temp where rowid = 2
delete @yourTable where name in (select name from @temp)
insert into @yourTable select * from @temp

-- after remove ------
select * from @yourTable order by name


REUSLT:

id name
----------- ----------------------------
1 bob smith
12 bob smith
3 eric brown
10 jane doe
2 jane doe
23 jane doe
12 jane doe
11 john deer


id name
----------- -----------------------------
12 bob smith
3 eric brown
2 jane doe
11 john deer

---

DELETE t
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY name order by id) as rowid
	from @yourTable)t
WHERE rowid >1

quote:

---

Originally posted by liagrisj

Sorry, I'm bad at sql since I never use it. I can't get the above code to run. Here is a snippet of the table I'm working with. Also, is there a way to do a select to show what's going to be deleted before I execute the delete statement? I've backed up the database before all my deletes.

----------------------------------------
driverID | driverName | driverCDL
----------------------------------------
2 | Bob Smith | 05401246
----------------------------------------
3 | Jane Doe | 48983468
----------------------------------------
4 | Eric Brown | 65498813

Thanks again for all your help.

---

quote:

---

Originally posted by liagrisj

Sorry, I'm bad at sql since I never use it. I can't get the above code to run. Here is a snippet of the table I'm working with. Also, is there a way to do a select to show what's going to be deleted before I execute the delete statement? I've backed up the database before all my deletes.

----------------------------------------
driverID | driverName | driverCDL
----------------------------------------
2 | Bob Smith | 05401246
----------------------------------------
3 | Jane Doe | 48983468
----------------------------------------
4 | Eric Brown | 65498813

Thanks again for all your help.

---

quote:

---

Originally posted by liagrisj


DELETE t
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY drivername order by driverid) as driverid
from @tbldriver)t
WHERE driverid >1


This is how I've changed it. When run, it states row_number is not a valid function. Obviously, I've screwed this up. I'm not sure which columns to put where? I only have three columns as shown in prior post.

---

quote:

---

Originally posted by liagrisj

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

We're upgrading to 2005 within the month. But we're still on the old for now.

---

quote:

---

Originally posted by liagrisj

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

We're upgrading to 2005 within the month. But we're still on the old for now.

---

DELETE t
FROM Table t
LEFT JOIN
(SELECT drivername,MIN(driverid) as mindriver
from Table 
GROUP BY drivername)t1
ON t1.drivername = t.drivername 
AND t1.mindriver = t.driverid
WHERE t1.drivername IS NULL