Duplicate Row to be deleted on the basis of a single field (1 Viewer)

Shiv

Registered User.
Local time
Today, 10:48
Joined
Dec 23, 2018
Messages
16
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 .
 

Attachments

  • Duplicate detail to be dleted on the basis of last field.accdb
    736 KB · Views: 95

cheekybuddha

AWF VIP
Local time
Today, 05:18
Joined
Jul 21, 2014
Messages
2,237
Hello,

Based on the table you posted you can use:
SQL:
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

..forever waiting... waiting for jellybean!
Local time
Today, 13:18
Joined
May 7, 2009
Messages
19,169
or you can just leave them
and only select records with [Count] = 1

select * from data where [count] = 1
 

Shiv

Registered User.
Local time
Today, 10:48
Joined
Dec 23, 2018
Messages
16
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

AWF VIP
Local time
Today, 05:18
Joined
Jul 21, 2014
Messages
2,237
Make a backup of your table first!!!

SQL:
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

New member
Local time
Today, 13:18
Joined
Apr 21, 2020
Messages
9
Retain the [Count] field. Just rename it into more valid name like "MyCount" because [Count] is reserved keyword.
 

cheekybuddha

AWF VIP
Local time
Today, 05:18
Joined
Jul 21, 2014
Messages
2,237
Or just to select as in Arne's example:
SQL:
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

..forever waiting... waiting for jellybean!
Local time
Today, 13:18
Joined
May 7, 2009
Messages
19,169
it's hard to find which one is the right Key,
anyway try this:
Code:
SELECT *
FROM DATA
WHERE Data.[DateTime] =  (SELECT MAX(T.[DateTime]) AS Expr1 FROM DATA AS T     
   WHERE ((T.FinalName) = Data.FinalName));
 

cheekybuddha

AWF VIP
Local time
Today, 05:18
Joined
Jul 21, 2014
Messages
2,237
My guess is that VEHNO might also be a suitable key, (just not with the redacted data in the example)
 

Users who are viewing this thread

Top Bottom