Another option (that works with SQL 2000) and even in cases of all the columns having the same value (no column to differentiate the rows), is inserting the result set into a new table with an identity column (or adding an identity column to the original table). After that, it's just a matter of keeping the distinct rows as shown in the comments.

I can't remember where I read this solution, but it was either here in SQL Server Central or SQL Team foruns.

André Cardoso