How to find max from a table by row

I have a Product table with the columns

AcctNum
ProdCode
InvoiceDate

I can have multiple rows for a given AcctNum:

123 A 01/01/2005
123 B 01/02/2005
123 C 01/03/2005
234 C 02/01/2004
345 A 01/01/2005
345 B 01/02/2005

I need the max(InvoiceDate) and if the max for a given AcctNum is a ProdCode
= B. So if the latest InvoiceDate is for a given AcctNum is B then return
that row.

123 B 01/02/2005
Would not be returned because the max Invoice date for AcctNum 123 is
ProdCode C.

345 B 01/02/2005
Would be returned because the max Invoice date for AcctNum 345 is ProdCode
B.

I can solve this using a cursor fairly easily by using a distinct AcctNum in
the cursor select and getting the max InvoiceDate for each AcctNum. This is
a costly and I'm looking for a solution using temp tables or a query to
handle this problem.

I hope I have made this clear enough (sorry if I was too verbose). Thanks in
advance for your help.

-pHi

Try

SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
FROM MyAccts A
WHERE ProdCode = 'B'
AND NOT EXISTS ( SELECT 1 FROM MyAccts B WHERE A.AcctNum = B.AcctNum AND
B.InvoiceDate > A.InvoiceDate )

or

SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
FROM MyAccts A
WHERE ProdCode = 'B'
AND A.InvoiceDate = ( SELECT MAX(B.InvoiceDate) FROM MyAccts B WHERE
A.AcctNum = B.AcctNum )

Also check out how to post DDL and example data at
http://www.aspfaq.com/etiquett*e.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.*htm#inserts
It is also useful to post your current attempts at solving the problem.

John
"Pippen" <name@.notreal.add> wrote in message
news:O9GdnTvxA-1jPqHfRVn-iw@.comcast.com...
>I have a Product table with the columns
> AcctNum
> ProdCode
> InvoiceDate
> I can have multiple rows for a given AcctNum:
> 123 A 01/01/2005
> 123 B 01/02/2005
> 123 C 01/03/2005
> 234 C 02/01/2004
> 345 A 01/01/2005
> 345 B 01/02/2005
> I need the max(InvoiceDate) and if the max for a given AcctNum is a
> ProdCode = B. So if the latest InvoiceDate is for a given AcctNum is B
> then return that row.
> 123 B 01/02/2005
> Would not be returned because the max Invoice date for AcctNum 123 is
> ProdCode C.
> 345 B 01/02/2005
> Would be returned because the max Invoice date for AcctNum 345 is ProdCode
> B.
> I can solve this using a cursor fairly easily by using a distinct AcctNum
> in the cursor select and getting the max InvoiceDate for each AcctNum.
> This is a costly and I'm looking for a solution using temp tables or a
> query to handle this problem.
> I hope I have made this clear enough (sorry if I was too verbose). Thanks
> in advance for your help.
> -p
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:423d73a3$0$32610$db0fefd9@.news.zen.co.uk...
> Hi
> Try
> SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
> FROM MyAccts A
> WHERE ProdCode = 'B'
> AND NOT EXISTS ( SELECT 1 FROM MyAccts B WHERE A.AcctNum = B.AcctNum AND
> B.InvoiceDate > A.InvoiceDate )
> or
> SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
> FROM MyAccts A
> WHERE ProdCode = 'B'
> AND A.InvoiceDate = ( SELECT MAX(B.InvoiceDate) FROM MyAccts B WHERE
> A.AcctNum = B.AcctNum )
> Also check out how to post DDL and example data at
> http://www.aspfaq.com/etiquett*e.asp?id=5006 and
> example data as insert statements
> http://vyaskn.tripod.com/code.*htm#inserts
> It is also useful to post your current attempts at solving the problem.
> John
> "Pippen" <name@.notreal.add> wrote in message
> news:O9GdnTvxA-1jPqHfRVn-iw@.comcast.com...
>>I have a Product table with the columns
>>
>> AcctNum
>> ProdCode
>> InvoiceDate
>>
>> I can have multiple rows for a given AcctNum:
>>
>> 123 A 01/01/2005
>> 123 B 01/02/2005
>> 123 C 01/03/2005
>> 234 C 02/01/2004
>> 345 A 01/01/2005
>> 345 B 01/02/2005
>>
>> I need the max(InvoiceDate) and if the max for a given AcctNum is a
>> ProdCode = B. So if the latest InvoiceDate is for a given AcctNum is B
>> then return that row.
>>
>> 123 B 01/02/2005
>> Would not be returned because the max Invoice date for AcctNum 123 is
>> ProdCode C.
>>
>> 345 B 01/02/2005
>> Would be returned because the max Invoice date for AcctNum 345 is
>> ProdCode B.
>>
>> I can solve this using a cursor fairly easily by using a distinct AcctNum
>> in the cursor select and getting the max InvoiceDate for each AcctNum.
>> This is a costly and I'm looking for a solution using temp tables or a
>> query to handle this problem.
>>
>> I hope I have made this clear enough (sorry if I was too verbose). Thanks
>> in advance for your help.
>>
>> -p
>
Thanks for the help.

-p