Compound index 1

[linousa]

Is it possible to create compound index with different orders:
field1 asc + field2 desc + field3 asc?

 

[TamarGranor]

You have to do tricks for stuff like that, something along the lines of:

INDEX ON Field1 + CHRTRAN(Field2, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'ZYXWVUTSRQPONMLKJIHGFEDCBA') + Field3

Tamar

 

[linousa]

Is it possible to get field4 with desc sequence number for the field2 on the fly? Or will need to loop it? Or run query?

field2  field4 
  z       4
  z       3
  z       2
  z       1
  y       3
  y       2
  y       1
...

 

[Mike Lewis]

Not quite clear what you want.

Do you want to sort on Field 4 within Field 1, with both fields descending? If so, you need to tell us what data type Field 4 is, and also its maximum size.

Assuming Field 4 is numeric, and can be up to three digits, You could do this:

[tt]INDEX ON Field1 + TRANSFORM(Field4, "999") DESCENDING[/tt]

Or, do you want Field 4 to be descending, within Field 1 ascending (which is not what your second post is showing, but is what your first post might be suggesting). If so, with the same assumptions as above:

[tt]INDEX ON Field1 + TRANSFORM(999 - Field4, "999")[/tt]

If neither of those is what you want, please clarify your question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Linousa, are you sure you actually need this index? If your overall aim is to get the data into a certain sequence in order to produce a report, or to display it in a grid, or anything similar, then there is another approach:

SELECT * FROM TheTable ORDER BY Field1 ASC, Field2 DESC, Field3 ASC ;
  INTO CURSOR SomeCursor

Then use SomeCursor as the data source for the report, grid or whatever.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads