Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Index on uniq 2 Examples

Status
Not open for further replies.

Hokis

Technical User
May 21, 2007
51
US
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
 
Not necessarily the best way, but this works:
Code:
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
 
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.
 
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:
Code:
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
 
thank you for the update, i'll try it.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top