Indexing a table on multiple fields with a date field in descending order

Indexing a table on multiple fields with a date field in descending order

Post by Jack McAfe » Mon, 14 Aug 2006 03:29:44


I have a table with the following fields: id, date, testrecord. What I want
to do is to order the table so that the records are ordered as follows: id
by date by testrecord. I want id to be in ascending order, but date and
testrecord in descending order. I created an index file called testdata with
the expression: id+dtoc(date,1)+testrecord. Id and testrecord are character
fields.This index file will sort in ascending order. My question is: what do
I need to do to get the table sorted as I want it - that is id in ascending
order, but date and testrecord in descending order?
 
 
 

Indexing a table on multiple fields with a date field in descending order

Post by Fred Taylo » Mon, 14 Aug 2006 04:36:15

Do you need an actual index, or just a "view" for reporting purposes? If
for reporting only, I'd suggest creating a cursor with a SELECT statement
using an ORDER BY since each field in the order by can be ascending or
descending as needed. If you really have to have an index, you can do:

id+TRANSFORM(99999999-VAL(DTOS(date))+testrecord

--
Fred
Microsoft Visual FoxPro MVP

 
 
 

Indexing a table on multiple fields with a date field in descending order

Post by AA » Mon, 14 Aug 2006 18:17:44

SELECT * FROM Mytable ORDER BY id ASC, date DESC, testrecord DESC INTO
CURSOR Q1
-Anders

"Jack McAfee" < XXXX@XXXXX.COM > skrev i meddelandet