Find the minimum date in a table

[make]

Hi Peggers,

iam trying to find the smallest date i one of my database tables.
I have a field named Pos_changed, the records a not sorted, so i have something like this:

Pos_changed :

01/01/02
03/01/01
02/02/00
03/05/02
and so on...

now im want to have the smallest date from this table.
How can i find that ?

Greets make

 

[Olivier_Desmars]

Use a FIND FIRST (or a FIND LAST) with an index using the field 'Pos_changed' (that's the fastest method) :

Example:


If your table has no index on pos_changed, create one. For example :

Index Name Cnt Field Name
----------- ---- -----------------------------------------
ix_date 1 + pos_changed


then, the following query will give you the good result :

FIND FIRST table USE-INDEX ix_date.


If you don't have the desired index, or if your table is small enough you can use the following :

DEFINE VARIABLE first-date AS DATE NO-UNDO.

FOR EACH <table-name> NO-LOCK BY pos_changed :
first-date = <table-name>.pos_changed.
LEAVE.
END.

-> the BY option specify that the table <table-name> must be sorted using the field 'pos_changed'
-> When the first record is found, the first date is stored in a variable and then we leave the FOR EACH loop (no need to read the other records)

Hope it can help you.

 

[make]

Thanks !! You helped a lot !

Thanks !! You helped a lot !