Index on uniq 2 Examples

[Hokis]

Hi I need to index on a field, but need 2 of each,( not just one of each) so is there a way to do that?
thanks
H

 

[JRB-Bldr]

Not necessarily the best way, but this works:

USE c:\temp\temp EXCLUSIVE
DELETE ALL
INDEX ON Agent_Code UNIQUE TAG uniq
RECALL all
SET ORDER TO
INDEX ON Agent_Code UNIQUE TAG uniq2 FOR DELETED()
RECALL all
SET ORDER TO
INDEX ON Agent_Code TAG code
BROWSE

You end up with all records deleted except the first 2 records for each Agent_Code.

A PACK would finally remove the deleted records.
Or you could do a:
SELECT * FROM Temp WHERE !DELETED() ORDER BY Agent_Code

Depending on how you want to actually use the INDEX, it may be what you need or not.

Remember that your SEEK() will land on the first record only.
And if the table is used as the Child table in a Relation, only the first record will be automatically 'seen'.

Good Luck,
JRB-Bldr

 

[Hokis]

ok i found out a better solution, I'de like to share with everyone.

Index on FieldName+STR(MOD(RECNO(),2)) To S1 uniq.

This will do the trick.
H.

 

[JRB-Bldr]

I don't think that your method would work in many situations.

Since your approach is dependent on the RECNO() which may be randomly entered into an even or odd RECNO(), you could end up with a non-desired result.

Test with the following:

CREATE TABLE c:\temp\temp  FREE (Name C(10),Address C(10))
* --- Populate Table with Test Values ---
FOR Cntr = 1 To 6
   APPEND BLANK
   IF MOD(RECNO(),2) = 0
      * --- Even Records ---
      REPLACE Name WITH "John";
        Address WITH "Addr" + STR(Cntr,2)
   ELSE
      * --- Odd Records ---
      REPLACE Name WITH "Bill";
        Address WITH "Addr" + STR(Cntr,2)
   ENDIF
ENDFOR

* --- Now Reverse The Sequence ---
FOR Cntr = 7 To 12
   APPEND BLANK
   IF MOD(RECNO(),2) = 0
      * --- Even Records ---
      REPLACE Name WITH "Bill";
        Address WITH "Addr" + STR(Cntr,2)
   ELSE
      * --- Odd Records ---
      REPLACE Name WITH "John";
        Address WITH "Addr" + STR(Cntr,2)
   ENDIF
ENDFOR

INDEX ON Name + STR(MOD(RECNO(),2)) UNIQUE TAG S1 
BROWSE

When I ran the above code I did indeed end up with 2 entries per value of 'Name', but the sequence was not sequential nor were they the TOP 2.

You might want to check your results for yourself.

Good Luck,
JRB-Bldr

 

[Hokis]

thank you for the update, i'll try it.