Max value in big table

[#1]

In my table 20 million row.

I need select max value for ID (then use this max value + autonumber() as next id).

Now this script:

MaxIDTable:

LOAD max(IdField) as maxIdField

RESIDENT MyTable;

But it is very slower. May be have another way for get max value field?

[#2]

I have in QVD file table with IdField, not order for this field.

I need add to this table new row - with generate for it new IdField as current max value idField + autonumber() as next id on new row.

AUTOGENERATE FieldValueCount('IdField') - it's not work for my table, because value in IdField like 1, 3, 5... and FieldValueCount('IdField') return 3 - but I want return 5.

[#3]

peek('IdField ',0,'TableNew'); = it's 40 second.  -- Here you are not loading entire table, you are just picking the value of last record

But for this I reorder table in new table TableNew - and it all 40 second.

[Gysbert_Wassenaar]

FieldValueCount returns the unique number of values that the field IdField contains. It is used in combination with Autogenerate to cycle trough all the values in the IdField. By cycling through all the values it becomes possible to find the max value with the max(FieldValue('IdField', recno())) expression. So FieldValueCount is not the max IdField value, but that's not what it is used for anyway.

[josevicentepla]

This question is asked in QV12DA Certificarton Exam for Data Architect