0

How to remove duplicate records from a view? I need to keep them on the physical table, but in the view, I don't want the duplicates

Here is the query I used:

 CREATE VIEW myview 
 AS 
     SELECT DISTINCT *
     FROM [roug].[dbo].[Table_1]
     ORDER BY id

for the table :

id| name age
----------
c1  ann  12
u2  joe  15
c1  ann  12
c1  ann  12
u5  dev  13
u3  Jim  16
u3  Jim  16
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anu Antony
  • 125
  • 1
  • 2
  • 12
  • Are you trying to remove the physical duplicates from the Table ? or you just need to see the records without duplicates in the view but wish to keep the records in the table ? – Jayasurya Satheesh Dec 18 '17 at 11:48
  • need to see the records without duplicates in the view. keep the records in the table remains constant @JayasuryaSatheesh – Anu Antony Dec 18 '17 at 11:51

2 Answers2

2

You can either use DISTINCT or ROW_NUMBER() Like this

create view myview as
WITH CTE
AS
(
    SELECT  
    RN = ROW_NUMBER() OVER(PARTITION BY [Id],[Name],[Age] ORDER BY ID),
    *
 FROM [roug].[dbo].[Table_1]
)
SELECT
    [Id],[Name],[Age]
    FROM CTE
        WHERE RN = 1
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. this is the error message getting @Jayasurya Satheesh – Anu Antony Dec 18 '17 at 11:40
  • @AnuAntony Try Without Order by.I have updated the answer – Jayasurya Satheesh Dec 18 '17 at 11:45
1

If you want to delete data then you should be doing it in the source table not the view. A standard approach for de-duping is via a cte. Try

;
  WITH  cte
          AS (SELECT    id
              ,         name
              ,         age
              ,         ROW_NUMBER() OVER (PARTITION BY id, name, age ORDER BY id) RN
              FROM      Table_1
             )
    DELETE  FROM cte
    WHERE   RN > 1

Depends on if you want to delete the actual data, or just not display it in the view.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • The question is to skip the duplicates from the view not to delete them from the table – Jayasurya Satheesh Dec 18 '17 at 11:53
  • No problem. Phrasing a question sometimes can be difficult. Both my answer and Jayasurya's both use the same technique, although I do wonder why DISTINCT is not working for you. Are there other fields involved in your real world use? Or hidden characters etc? A simple distinct should be fine just for selection. – Matthew Baker Dec 18 '17 at 11:59