Posted by Ed Murphy on 11/28/07 05:38

bwalton_707@yahoo.com wrote:

> On Nov 27, 3:32 pm, --CELKO-- <jcelko...@earthlink.net> wrote:

>> This is the usual template for finding the latest invoice is:
>>
>> SELECT I1.invoice_nbr, C.customer_name, C.shopping_addr, C.acct_nbr
>> FROM Invoices AS I1,
>> Customers AS C
>> WHERE I1.acct_nbr = C.acct_nbr
>> AND I1.posting_date
>> = (SELECT MAX(I2.posting_date)
>> FROM Invoices AS I2
>> WHERE I2.acct_nbr = C.acct_nbr);

(Side note: Please trim quotes down to just the part that's
immediately relevant to your reply, like I've done here.)

> Thanks for the reply, unforunately your solution is not completely
> accurate because it will return multiple records
> if they have the same datetime (posted) date which is not the desired
> result.

Assuming that you want the acct_nbr's lowest invoice_nbr with the most
recent posting_date:

SELECT I1.invoice_nbr, C.customer_name, C.shopping_addr, C.acct_nbr
FROM Invoices AS I1,
Customers AS C
WHERE I1.acct_nbr = C.acct_nbr
AND I1.invoice_nbr
= (SELECT MIN(I2.invoice_nbr)
FROM Invoices AS I2
WHERE I2.acct_nbr = C.acct_nbr
AND I2.posting_date
= (SELECT MAX(I3.posting_date)
FROM Invoices AS I3
WHERE I3.acct_nbr = C.acct_nbr));

• Next in forum: Re: Not Exists joining 2 tables
• Prev in forum: Re: SQL Express - Identity specification property - how to change
• Thread view: Re: ORDER BY AND GROUP BY CLAUSE

[Reply to this message]