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

cheekybuddha

AWF VIP
Local time
Today, 00:07
Joined
Jul 21, 2014
Messages
353
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

error reading drive A:
Local time
Today, 07:07
Joined
May 7, 2009
Messages
9,273
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, 04:37
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, 00:07
Joined
Jul 21, 2014
Messages
353
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, 07:07
Joined
Apr 21, 2020
Messages
1
Retain the [Count] field. Just rename it into more valid name like "MyCount" because [Count] is reserved keyword.
 

cheekybuddha

AWF VIP
Local time
Today, 00:07
Joined
Jul 21, 2014
Messages
353
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

error reading drive A:
Local time
Today, 07:07
Joined
May 7, 2009
Messages
9,273
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, 00:07
Joined
Jul 21, 2014
Messages
353
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 (Users: 0, Guests: 1)

Top Bottom