Difficulty writing SQL to delete records matching criteria. 2

[VickyC]

hello to all

I have a table with an autonumber PK (K_ID) and 10 integer fields (K9, K8, K7....K0).

Here are typical records.[tt]

K_ID K9 K8 K7 K6 K5 K4 K3 K2 K1 K0
...
1087645 53 38 38 38 38 38 35 22 14 14
1087646 53 38 38 38 38 35 35 22 14 14
1087647 53 44 38 38 35 35 35 35 35 35
... etc [/tt]

In each record, each value (K9 to K0) is greater or equal to the value to its right.

I am trying to figure out the SQL to delete those records that have MORE THAN 4 identical values. In the example shown, the top row should be deleted (because it has five 38s), and the bottom row should be deleted (because it has six 35s).

I can do this in VBA, I think, but I'm looking for an SQL solution. The tables are HUGE, and I suspect the SQL solution will be much quicker.

thanks in advance for any assistance
Vicky C.

 

[PHV]

DELETE FROM yourTable
WHERE (K1=K2 AND K2=K3 AND K3=K4 AND K4=K5)
OR (K2=K3 AND K3=K4 AND K4=K5 AND K5=K6)
OR (K3=K4 AND K4=K5 AND K5=K6 AND k6=K7)
OR (K4=K5 AND K5=K6 AND k6=K7 AND K7=K8)
OR (K5=K6 AND k6=K7 AND K7=K8 AND K8=K9)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

PHV - This will work just fine. In the actual problem, the number of consecutive identical values that force deletion can vary according to other factors. Your solution will be easy to adapt to handle all of these situations. Thanks!

Vicky C.

 

[JonFer]

If the table values are always sorted K9 to K0 then you can just check that K9=K5 to check there are 5 duplicate values there. Similarly with K8 through K5 - just check the 5th value away.

 

[PHV]

You're right JonFer, very more efficient !

DELETE FROM yourTable
WHERE K1=K5 OR K2=K6 OR K3=K7 OR K4=K8 OR K5=K9

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886