How to Get Latest Invoice for Each Customer Number

ID: Q105141

The information in this article applies to:

• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, and 2.5a
• Microsoft FoxPro for Windows, versions 2.5, and 2.5a

Customers frequently ask how to pull certain information out of a database that is the latest or most current information, usually based on some other field that may be duplicated in the same database.

While there are many different methods that might be employed, including using DO WHILE loops, creating a program or procedure with the following SELECT statements will achieve the quickest result (the INVOICES.DBF file in the TUTORIAL subdirectory has been used in this example because it gives a good approximation of this type of database structure):

   SELECT invoices.cno, MAX(invoices.idate) AS idate
      FROM invoices;
      GROUP BY invoices.cno;
      INTO cursor temp

   SELECT invoices.*;
      FROM invoices, temp;
      WHERE invoices.cno = temp.cno;
      AND invoices.idate = temp.idate;
      ORDER BY invoices.cno