Multiple nths within one table

[pkdata]

I have a table of 128k+ records and need to nth out based on values in one field called "dg". I need to nth to 10 records per value in the column dg. Any suggestions?

 

[EzLogic]

I am not sure i udnerstand what you're looking for. what do you mean you want to nth to 10 recs per value?



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[pkdata]

I have as many as 30 records with the same value in the dg field and I only need ten records for each group where the value is the same.
Example: 27 records have a dg value of 1234 and I only want to retain 10 of those records
12 records have a dg value of 5678 and again, I only want to retain 10 of those records
and so on. Does that make sense or is it still clear as mud?

Thanks for the assistance. I know I have written something for this before, but I just can't get my head wrapped around it this time.

 

[pkdata]

You know, it seems as if I recall assigning a sequential number for each value and then deleting anything greater than 10. Does that sound like it would work?

 

[Olaf Doschke]

A sequential number would help. But you could also count while scanning with order set to the dg field.

Bye, Olaf.

 

[Andre Globensky]

I think that this should do what you want.

Add a field final C(10) (you could also go with a logical field if you don't need a sequence)
run this code

FOR lni = 1 TO 10

INDEX ON gd TAG gd UNIQUE FOR EMPTY(final)

REPLACE ALL final WITH TRANSFORM((lni))

DELETE TAG mailcode

ENDFOR

André

 

[Andre Globensky]

typo on my delete tag, ran it on a test table
DELETE TAG gd

 

[Olaf Doschke]

A UNIQUE index will only put the first record of a certain gd record into the index, so your code will keep the first records of the first 10 gd values. That's not what pkdata specified.

Untested code:

select table
set order to dg && assume you have an index on that field
dgcurrent = dg
counter=0
scan
   If dgcurrent < dg
      dgcurrent = dg
      counter = 1
   else
      counter = counter + 1
   endif
   if counter > 10
      scan while dg=dgcurrent
         delete
      endscan
   endif
endscan

You might want to scan with an order not just sorting by dg, but by dg and a secondary field, eg a datetime. Then you need a compound index, whcih combines dg and that other field via an expression concatenating them, eg str(dg)+dtos(datefield).

Bye, Olaf.

 

[Andre Globensky]

Hello Olaf,
not with the FOR EMPTY(final) clause, once it's flagged (REPLACE ALL final WITH TRANSFORM((lni))), it's not coming back, so you get a new record after

 

[Olaf Doschke]

AGlbensky,

I see your intention. Ok, this will work as you delete and regenerate the index.
But in the end the 11th and further records are not removed. You need a final statement DELETE FOR EMPTY(Final).

The idea is quite elegant, but this will create, delete and recreate the index etc 10 times, one scan loop should be faster, and it doesn't need any additional field.

I'd just modify it a bit, as there also is DELETE WHILE and as set order to does not necessarily move to the first, lowest dg value.

select table
set order to dg && assume you have an index on that field
dgcurrent = -9999 && anything lower than any dg value could ever become
scan
   If dgcurrent < dg && entereing the scan this must be true, as dgcurrent is initialized lower than any dg value
      dgcurrent = dg
      counter = 1
   else
      counter = counter + 1
   endif
   if counter > 10
      delete while dg=dgcurrent
   endif
endscan

Bye, Olaf.

 

[Andre Globensky]

hmm, thanks, and oups yes i did miss the "out" of his question "need to nth out".
I thought he wanted to flag them for further use. Anyways, i prefer to flag them and put them aside if by any chance they need them.

 

[Olaf Doschke]

Once more, now tested with test data added:

* preparing test data
Create Cursor curTest (dg I)
For lni=1 To 3
   For lnj=1 To 14
      Insert Into curTest Values(lni)
   Endfor
Endfor
Index On dg Tag dg
*----
Set Deleted On
Set Order To dg && an index on that field must exist
dgcurrent = -9999 && anything lower than any dg value could ever become
Scan
   If dgcurrent < dg && entereing the scan this must be true, as dgcurrent is initialized lower than any dg value
      dgcurrent = dg
      counter = 1
   Else
      counter = counter + 1
   Endif
   If counter > 10
      Delete While dg=dgcurrent
      * after the delete, the record pointer already points to the next dg value
      dgcurrent = dg
      counter = 1
   Endif
Endscan
Set Deleted Off
Browse

Bye, Olaf.

 

[pkdata]

Thank you Olaf! Works great!