sql query - eliminate all duplicates but one
-
hi guys.... i have this table ID_____NAME______OTHER_____ 1_____Name 1__First value 2_____Name 2__Second value 3_____Name 2__Second value 4_____Name 2__Second value 5_____Name 2__Second value 6_____Name 3__Third value 7_____Name 3__Third value 8_____Name 4__Fourth value i need to make SQL query wich will eliminate all duplicates from table (all fields are comparing, except id) but one. for example, after query is executed table should look like this: ID_____NAME______OTHER_____ 1_____Name 1__First value 2_____Name 2__Second value 6_____Name 3__Third value 8_____Name 4__Fourth value any help appreciated...
-
hi guys.... i have this table ID_____NAME______OTHER_____ 1_____Name 1__First value 2_____Name 2__Second value 3_____Name 2__Second value 4_____Name 2__Second value 5_____Name 2__Second value 6_____Name 3__Third value 7_____Name 3__Third value 8_____Name 4__Fourth value i need to make SQL query wich will eliminate all duplicates from table (all fields are comparing, except id) but one. for example, after query is executed table should look like this: ID_____NAME______OTHER_____ 1_____Name 1__First value 2_____Name 2__Second value 6_____Name 3__Third value 8_____Name 4__Fourth value any help appreciated...
Are you using Microsoft SQL Server? If yes, you could use the following query (you have to adopt it to your db):
;WITH MyTable_CTE(Name, Ranking)
AS
(
SELECT Name,Ranking = DENSE_RANK() OVER (PARTITION BY Name ORDER BY NEWID() ASC)
FROM MyTable
)
DELETE FROM MyTable_CTE
WHERE Ranking > 1Hope this helps Regards Sebastian
It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland.