>>>>>>>Hello,
>>>>>>>
>>>>>>> How to find the position of a record in an indexed table.
>>>>>>>RECNO() will give the position of the record in the table but how to find the position in an indexed table.
>>>>>>>
>>>>>>>TIA
>>>>>>
>>>>>>For Versions of VFP previous to 9, if the table is small (under 65,000 rows), you could do a SQL SELECT of the primary keys into an array in the order of the index key. Then do an ASCAN to find your primary key. The element of the array would be the indexed order.
>>>>>
>>>>>Fred! That's brilliant!
>>>>>
>>>>
>>>>Sure ?
>>>
>>>It is a great concept.
>>>
>>
>>no, it is useless.
>
>You and Fred are doing the same concept in different ways. Different implementation. Your implementation may be better, but the concept is the same.
>
>>
>>>>ASCAN is equal to a ordered count
>>>
>>>Yes.
>>
>>
>>>
>>>>
>>>>>You could expand that to a cursor! select pk order by indexkey. locate for pk = m.pk
>>>>>
>>>>
>>>>If you want gain something:
>>>>

>>>>SELECT pk from table order by 1 ...
>>>>INDEX ON pk tag tagpos && without this, the locate is equal to the ordered count, again
>>>>SEEK m.pk
>>>>? recno()
>>>>

>>>
>>>Only if the index is used in several subsequent seeks.
>>>
>>
>>without the index, this solution is useless
>>
>>>>
>>>>>recno is then the relative position.
>
>Con tropo rispetto, mio amico, non sono d'accordo
>
>

CREATE TABLE SPEED.DBF FREE ;
>  (Spd_ID I, ;
>  Spd_Char C(10), ;
>  Spd_Date D, ;
>  Spd_DT T)
>
>LOCAL ;
>  m.lnI, ;
>  m.lnX, ;
>  m.Spd_ID, ;
>  m.Spd_Char, ;
>  m.Spd_Date, ;
>  m.Spd_DT
>
>FOR m.lnI = 1 TO 500000
>  m.SPD_ID = m.lnI
>  m.SPD_CHAR = TRANSFORM(m.lnI,"##########")
>  *This will give me groups of records
>  *with the same date / date time.
>  m.lnX = m.lnI / 100
>  m.SPD_DATE = {^2000-01-01} + m.lnX
>  m.SPD_DT = {^2000-01-01 00:00:00} + m.lnX
>  INSERT INTO SPEED FROM MEMVAR
>  IF MOD(m.lnI,1000) = 0
>    WAIT WINDOW TRANSFORM(m.lnI) NOWAIT
>  ENDIF
>ENDFOR
>INDEX ON SPD_ID TAG SPD_ID
>INDEX ON SPD_DATE TAG SPD_DATE
>INDEX ON SPD_DT TAG SPD_DT
>INDEX ON VAL(SPD_CHAR) TAG VALCHAR
>CLEAR ALL
>
>a=SECONDS()
>SELECT * from speed order by spd_date descending into cursor c_fabio
>LOCATE FOR spd_id = 500000 NOOPTIMIZE
>?"locate:",RECNO(),SECONDS()-m.a
>
>a=SECONDS()

* WRONG: the table order must respect the index order
>SELECT * from speed order by spd_date *** DESCENDING **** into cursor c_fabio
>INDEX on spd_id TAG spd_id DESCENDING
>SEEK 500000
>?"seek:",RECNO(),SECONDS()-m.a
>
>

what I try to explain to you is that the overload time for the select is justified only if they serve a lot of positions, and the native table doesn't change.
Besides, if the index (spd_date) it is not univocal then the value it is not correct.

CREATE TABLE SPEED.DBF FREE ;
  (Spd_ID I, ;
  Spd_Char C(10), ;
  Spd_Date D, ;
  Spd_DT T)

LOCAL ;
  m.lnI, ;
  m.lnX, ;
  m.Spd_ID, ;
  m.Spd_Char, ;
  m.Spd_Date, ;
  m.Spd_DT

FOR m.lnI = 1 TO 500000
  SPD_ID = m.lnI
  SPD_CHAR = TRANSFORM(m.lnI,"##########")
  *This will give me groups of records
  *with the same date / date time.

  SPD_DATE = {^2000-01-01} + RAND()*10000
  SPD_DT = {^2000-01-01 00:00:00} + RAND()*10000
  INSERT INTO SPEED FROM MEMVAR
  IF MOD(m.lnI,1000) = 0
    WAIT WINDOW TRANSFORM(m.lnI) NOWAIT
  ENDIF
ENDFOR
INDEX ON SPD_ID TAG SPD_ID
INDEX ON SPD_DATE TAG SPD_DATE
INDEX ON SPD_DT TAG SPD_DT
INDEX ON VAL(SPD_CHAR) TAG VALCHAR
CLEAR ALL

CLEAR

USE SPEED.dbf
SET ORDER TO SPD_DATE DESCENDING
GO TOP
* GET THE id in middle position
SKIP RECCOUNT()/2-1
IDSEARCHED=SPD_ID 
? "id in middle position of  spd_date is",m.IDSEARCHED
?

**** START TEST *****
#define SEARCHCOUNT 5

* I do the task directly
SELECT SPEED
a=SECONDS()
SELECT 0
USE SPEED.DBF AGAIN ORDER SPD_DATE DESCENDING
?"direct overload:",SECONDS()-m.a at 30

a=SECONDS()
FOR s=1 TO SEARCHCOUNT
	COUNT ALL WHILE spd_id<>m.IDSEARCHED
	pos =_TALLY+1
NEXT
??[count x SEARCHCOUNT:] at 55,m.pos,SECONDS()-m.a

a=SECONDS()
* copy the whole record has not sense !!
SELECT spd_id from speed order by spd_date descending into array c_fred
?"ascan overload:",SECONDS()-m.a at 30

a=SECONDS()

FOR s=1 TO SEARCHCOUNT
	pos=ASCAN(c_fred, m.IDSEARCHED )
NEXT
??[ascan x SEARCHCOUNT:] at 55,m.pos,SECONDS()-m.a
RELEASE c_fred

a=SECONDS()
* copy the whole record has not sense
SELECT spd_id from speed order by spd_date descending into cursor c_Mike
?"copy ordered overload:",SECONDS()-m.a at 30

a=SECONDS()
FOR s=1 TO SEARCHCOUNT
	LOCATE FOR spd_id = m.IDSEARCHED
	pos=RECNO()
NEXT
??[locate x SEARCHCOUNT:] at 55,m.pos,SECONDS()-m.a

a=SECONDS()
* search 10 times
FOR s=1 TO SEARCHCOUNT
	LOOKUP(spd_id,m.IDSEARCHED,spd_id)
	pos=RECNO()
NEXT
?[LOOKUP x SEARCHCOUNT:] at 55,m.pos,SECONDS()-m.a

a=SECONDS()
* copy the whole record has not sense
SELECT spd_id from speed order by spd_date DESCENDING into cursor c_fabio
INDEX on spd_id TAG spd_id DESCENDING

?"copy and index ordered overload:",SECONDS()-m.a at 30

a=SECONDS()
FOR s=1 TO SEARCHCOUNT
	SEEK m.IDSEARCHED
	pos=RECNO()
NEXT
??[seek x SEARCHCOUNT:] at 55,m.pos,SECONDS()-m.a



CLOSE TABLES ALL