A database index allows the database manager to retrieve records quickly by looking up only the values of one or more key fields stored in separate database blocks from the records themselves, which then point to the location where the records are stored.

And what are the indexes of the Order table?

To display the Order table indexes:

  1. From the Tools menu, select Data Dictionary.
  2. Select the Order table from the list of tables, then click the Indexes button:
  3. Click the Index Properties button. The Index Properties dialog box appears and shows the properties of the first index, CustOrder:

    This is the index the AVM uses to retrieve the Orders, because its first component is the CustNum field, and that is the field it has to match against the CustNum from the Customer table. Since the other component in the index is the OrderNum field, this index sorts records by OrderNum within CustNum so your request for the FIRST Order returns the record with the lowest Order number.

  4. Exit the Data Dictionary before you continue. Otherwise, OpenEdge will not let you run any procedures, because it has a database transaction open and ready to save any changes you might make in the Data Dictionary.

    The following figure shows the beginning of the display from the block FOR EACH Customer WHERE State = "NH", FIRST Order OF Customer.

    Figure 1. Lowest Order number for each Customer

As expected, you see the Order with the lowest order number for each Customer. If what you want is the earliest Order date, this output might not give you the information you are looking for.

Adding a BY phrase to the statement does not help because the AVM retrieves the records before applying the sort. So if you want the Order with the earliest Order date, it will not work to do this:

FOR EACH Customer NO-LOCK WHERE Customer.State = "NH",
  FIRST Order OF Customer NO-LOCK BY Order.OrderDate:
  DISPLAY Customer.CustNum Customer.Name Order.OrderNum Order.OrderDate.
END.

This code retrieves the same Orders as before, but then sorts the whole result set by the OrderDate field, as shown in the following figure.

Figure 2. Orders sorted by OrderDate