How to Use SQL to Get the Last Two Invoices for Each Customer

ID: Q129535

The information in this article applies to:

Microsoft FoxPro for Windows, version 2.6a

SUMMARY

This article shows by example how to use a subquery in the SELECT-SQL command to find the last two invoices for each customer (according to date in this example).

MORE INFORMATION

This example uses the INVOICES.DBF table in the FPW26\TUTORIAL directory.

Code Sample

SELECT cno, ino, MAX(idate) ;

   FROM invoices ;
   WHERE invoices.idate < ;
     (SELECT MAX(temp.idate) FROM invoices temp ;
      WHERE temp.cno = invoices.cno) ;
   GROUP BY cno ;

UNION ; SELECT cno, ino, MAX(idate) ;

   FROM invoices ;
   GROUP BY cno ;
   ORDER BY 1, 3 ;
   INTO CURSOR last2inv

BROWSE

Explanation of Code Sample

The second SELECT (everything after the UNION) retrieves the last invoice by date.

The first SELECT (everything before the UNION) retrieves the second from the last invoice by date. The key to this part of the query working correctly is the use of the alias name in the subquery so that SQL command will open the Invoices table again in another workarea under the alias specified. This is necessary so that the subquery returns only one record, and that record will be for the current customer number in the query process. The alias is specified in the FROM clause as shown here:

   FROM <table name> <alias>

NOTE: This strategy of using the alias name is often used when implementing a self-join (when a table is joined with itself).

Additional reference words: FoxWin 2.60a KBCategory: kbprg kbcode KBSubcategory: FxprgSql

Last Reviewed: June 27, 1995