Duplicate Row to be deleted on the basis of a single field

[Shiv]

Greetings to everyone !

I want to delete the Duplicate rows on the basis of a field named as Finalname. i want to do it through SQL query therefore pls favor .

 

[cheekybuddha]

Hello,

Based on the table you posted you can use:

DELETE
  *
FROM [Data]
WHERE [Count] > 1;

On a separate note, it is a bad idea to name table fields 'DateTime' and 'Count'. They are reserved words and can cause issues that are difficult to diagnose. Iwould suggest changing these names if you can.

hth,

d

 

[arnelgp]

or you can just leave them
and only select records with [Count] = 1

select * from data where [count] = 1

 

[Shiv]

or you can just leave them
and only select records with [Count] = 1

select * from data where [count] = 1

Thanks sir for this favour. How can we get the same result when count FIeld is not available in the table

 

[cheekybuddha]

Make a backup of your table first!!!

DELETE
  d.*
FROM [Data] d
LEFT JOIN (
SELECT
  FinalName,
  MAX([DateTime]) AS MaxDateTime
FROM [Data]
GROUP BY 
  FinalName
ORDER BY
  FinalName
) t
       ON d.FinalName = t.FinalName
      AND d.[DateTime] = t.MaxDateTime
WHERE t.FinalName IS NULL;

 

[BuyoyWebDev]

Retain the [Count] field. Just rename it into more valid name like "MyCount" because [Count] is reserved keyword.

 

[cheekybuddha]

Or just to select as in Arne's example:

SELECT
  d.*
FROM [Data] d
LEFT JOIN (
SELECT
  FinalName,
  MAX([DateTime]) AS MaxDateTime
FROM [Data]
GROUP BY 
  FinalName
ORDER BY
  FinalName
) t
       ON d.FinalName = t.FinalName
      AND d.[DateTime] = t.MaxDateTime
WHERE t.FinalName IS NOT NULL;

(Note the WHERE clause is different from the DELETE version)

 

[arnelgp]

it's hard to find which one is the right Key,
anyway try this:

SELECT *
FROM DATA
WHERE Data.[DateTime] =  (SELECT MAX(T.[DateTime]) AS Expr1 FROM DATA AS T     
   WHERE ((T.FinalName) = Data.FinalName));

 

[cheekybuddha]

My guess is that VEHNO might also be a suitable key, (just not with the redacted data in the example)