A unique row identifier is required to perform a set-based delete of the
duplicates in a single query. Without one, you will need either a cursor or
some other sort of iteration technique (e.g. WHILE).

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Macio" <othellot (AT) example (DOT) com> wrote

Here is one method for SQL Server 2005. Adding an UNIQUE constraint after
the delete will make life easier.

;WITH DupOrders (rn)
AS
(SELECT ROW_NUMBER() OVER(
PARTITION BY order_nbr
ORDER BY date)
FROM Orders)
DELETE FROM DupOrders
WHERE rn > 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Wed, 13 Feb 2008 08:30:16 +0100, Macio <othellot (AT) example (DOT) com>
wrote:

That sounds like a terrible database design.
You could use:
select distinct order, amount, [date]
to get the unique records, and insert them into a new table, then
delete the existing table and rename the new one to the current name.

-Tom.

The WHILE loop suggested by Dan would work like this:

WHILE EXISTS (
SELECT 1
FROM table
GROUP BY order, amount, date
HAVING COUNT(*) > 1
)
BEGIN
DELETE TOP(1) t
FROM table
WHERE EXISTS (
SELECT 1
FROM table
WHERE order = t.order
AND amount = t.amount
AND date = t.date
GROUP BY order, amount, date
HAVING COUNT(*) > 1
)
END

This also only works on SQL Server 2005. On SQL Server 2000 I'd
recommend using a temp table and SELECT DISTINCT as Tom suggested.

Good luck!
J

I missed the alias assignment

On Feb 14, 3:55*pm, jhofm... (AT) googlemail (DOT) com wrote:
J