selecting the record before the last one

Hi,

I've been breaking my head over this problem and getting nowhere. To explain the problem, I'll have to give a bit of background, so bear with me.

I have this system set up with postgres and php. One of the things this system does is allow the user to enter invoices. Invoice info is entered into the "cart" table and the "cart_item" table. Invoices can be paid in numerous ways, including by cheque. Sometimes, the cheque amount will exceed the invoice total. This necessitates creating a credit, which is the difference between the cheque amount and the invoice amount. This amount is stored in a separate table called "difference". In addition to the amount, the "difference" table includes the customer's id (clientid) and the invoice id (cartid).

Let's say a customer places an order. The user enters the order and when he gets to the invoice page, finds a credit from the previous invoice which the customer over paid. To determine if the customer has a credit, the system does the following:

// is there a credit/debit associated with this customer
$query = "SELECT * FROM difference WHERE clientid = $CLIENTID";
$result = pg_query($db_conn, $query) or die("Error in query: $query."); // exec query
$numrows = pg_numrows($result); // get number of rows
if ($numrows == 1) // yes
{
// access credit amount and apply to present invoice
}

Now, what happens when the customer has placed several orders over a period of time, and overpaid for each one? To access the pertinent credit amount, I did the following which I think solved the problem:

// is there a credit/debit associated with this customer
$query = "SELECT * FROM difference WHERE clientid = $CLIENTID ORDER BY cartid ASC LIMIT 1";
$result = pg_query($db_conn, $query) or die("Error in query: $query."); // exec query
$numrows = pg_numrows($result); // get number of rows
if ($numrows == 1) // yes
{
// access credit amount and apply to present invoice
}

By adding the "ORDER BY cartid ASC LIMIT 1" to the sql statement, the user gets the most recent credit amount for that customer entered into the system. That is, the credit amount created by the previous invoice.

On the other hand, if the user wants to view an older invoice with its associated credit, he would do:

$query = "SELECT * FROM difference WHERE clientid = $CLIENTID AND cartid = $CARTID";
...etc...

This is all fine. My big problem is that I have two different sql statements. One to determine if there's a credit for the invoice currently being entered; another to determine if there's a credit for a previously created invoice; and no way to determine when to use one or the other.

You may ask, why can't I search the cartid in both instances. The reason is, when the user is creating an invoice, that invoice's cartid (which is a random number) is already entered into the system, and therefore if he searches by clientid and cartid, he'd get back the same invoice he's currently entering. If, on the other hand, the user is looking up a previous invoice, that invoice includes both clientid and cartid, allowing him to search directly for an associated credit.

So my question is, is there a way to have one sql statement that serves both contexts?

Greatly looking forward to your feedback.

Mark