Deleting Duplicate Records From The Table in Different Way

Hi All,

Here I am going to explain how to DELETE duplicate records from table using CTE and Row_Number()

Table and Data Script :
CREATE TABLE [dbo].[Deleting Duplicate Record](
[Id] [int] NULL,
[Name] [varchar](50) NULL,
[Department] [varchar](50) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (1, N'Santosh', N'BI', CAST(0x0000A1F701592FF6 AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (1, N'Santosh', N'BI', CAST(0x0000A1F70159578E AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (2, N'Santosh', N'MSSQL', CAST(0x0000A1F701597B6E AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (2, N'Santosh', N'MSSQL', CAST(0x0000A1F7015987FC AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (3, N'Krishna', N'JAVA', CAST(0x0000A1F70159BC02 AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (4, N'Krishna', N'SPRING', CAST(0x0000A1F70159D1F6 AS DateTime))
GO


Solution 1: Here i am Using CreatedDate column for order to give row number , by default it take ASC order.

;WITH Duplicate as
(SELECT *,row_number() OVER(partition by Id,Name,Department order by CreatedDate) as Numbering  from [Deleting Duplicate Record])

DELETE FROM Duplicate WHERE Numbering>1

Here i am deleting the records based on Createddate,means which row is inserted last that one is going to delete.

This is common and every one can DELETE rows based on the created date but I want to DELETE rows WITH OUT using the Createddate column.

Solution 2: Here i am Deleting the Duplicate rows WITH OUT using the CreatedDate Column,and deleting in inserted order only.

;WITH Duplicate as
(SELECT *,row_number() OVER(partition by Id,Name,Department order by (SELECT 0)) as Numbering  from [Deleting Duplicate Record])

DELETE FROM Duplicate WHERE Numbering>1

Here i am just adding SELECT 0 to Order by Clause so that it will give u the row number as inserted order.

this is little bit different way to delete duplicate records from the table.


Thanks For Visiting Blog.

With Out USING Union We Can Get the SUM Of Rows At Group Level and Whole Records At Bottom

Hi All,


Most of the time we will use the UNION to get the whole sum of records group by some column and showing at each group level sum.

But in this Case we need to hit the database two times , then also  we cant get the whole Records sum, for that again we need to add one more UNION.

So to over come this situation i used the ROLLUP. 

SCRIPT:

CREATE TABLE [dbo].[SUMING_Data](
[ID] [int] NULL,
[Amount] [int] NULL,
[NAME] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 100, N'x')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 200, N'y')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 300, N'z')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (2, 200, N'p')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (4, 200, N'q')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (2, 400, N'r')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (4, 600, N's')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (3, 100, N't')

GO

SELECT * FROM [SUMING_Data]

in the above screen each color represent the different group name

But i want out put like each group level sum and all rows sum at the bottom.

for that previously i used UNION 

THIS IS USING UNION, HERE WE NEED TO WRITE TWO QUIRES


SELECT * FROM SUMING_Data
UNION
SELECT [ID],sum([Amount]),'Total' FROM SUMING_Data GROUP BY [ID]

Problem:

In the above screen shot red line showing the individual amount ,and blue one showing the Group Total 
But if i want all the records total , again i need to add one more UNION and one more hit to the database 

To overcome the above problem follow the below query.

Solution:

WITH OUT USING UNION ALSO WE CAN DO THIS ,USING THIS OUR QUERY IS VERY SIMPLE

Method 1:

SELECT  ID, SUM(AMOUNT) Amount, ISNULL(NAME,'TOTAL')GroupLevel  
FROM    SUMING_Data  
GROUP BY  ID,NAME 
WITH  ROLLUP

if we are using  isnull query we will get below out put:

Method 2:

SELECT ID, SUM(AMOUNT) Amount, GROUPING(name) GroupLevel 
FROM  SUMING_Data  
GROUP BY  ID,name 
WITH  ROLLUP

If we are using the Grouping we will get below out put ,here 0 indicates the individual amount and 1 indicates the SUM amount and at the bottom u can find the Total Amount in both Outputs

This is all about RollUP in SQL 

Thanks For Visiting Blog.