Delete Duplicate Rows in Sql Server

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4

SELECT *
FROM DuplicateRcordTable
/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1

Comments

Post a Comment

Popular posts from this blog

How to Convert Word Document to PDF using C#

  Document wordDocument = null;             string Sourcepath = ConfigurationSettings.AppSettings["SourcePath"];             string DestinationPath = ConfigurationSettings.AppSettings["DestinationPath"];             DirectoryInfo dirInfo = new DirectoryInfo(Sourcepath);             FileInfo[] wordFiles = dirInfo.GetFiles("*.doc");             foreach (FileInfo wordFile in wordFiles)             {                 object paramSourceDocPath = (Object)wordFile.FullName;                 //(Object)wordFile.Na...
Read more

How to Implement a Simple Chatbot Using ChatGPT in a C# WinForms Application?

  Here's a step-by-step guide to create a basic chatbot using ChatGPT in a C# WinForms application: Set Up the Project : Create a new C# WinForms App in Visual Studio. Install System.Net.Http and Newtonsoft.Json via NuGet Package Manager. Design the Form : Add a TextBox for user input ( txtUserInput ). Add a Button to send the message ( btnSend ). Add a TextBox for displaying the chat conversation ( txtChat ), set its Multiline property to True . Code Example : using System; using System.Net.Http; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using Newtonsoft.Json; namespace ChatGPTWinForms {     public partial class Form1 : Form     {         private static readonly string apiKey = "YOUR_API_KEY_HERE";         private static readonly string endpoint = "https://api.openai.com/v1/completions";         public Form1...
Read more

Sql query to get counts by quarterly and half yearly

---- Quarterly  SELECT     CASE         WHEN MONTH(date_column) IN (1, 2, 3) THEN 'Q1'         WHEN MONTH(date_column) IN (4, 5, 6) THEN 'Q2'         WHEN MONTH(date_column) IN (7, 8, 9) THEN 'Q3'         WHEN MONTH(date_column) IN (10, 11, 12) THEN 'Q4'     END AS quarter,     CONCAT(YEAR(date_column),         CASE             WHEN MONTH(date_column) IN (1, 2, 3, 4, 5, 6) THEN '-H1'             WHEN MONTH(date_column) IN (7, 8, 9, 10, 11, 12) THEN '-H2'         END) AS half_year,     COUNT(*) AS count FROM your_table GROUP BY quarter, half_year ORDER BY quarter, half_year; --- Half Yearly  SELECT YEAR(d...
Read more