How to find max from a table by row

[Pippen]

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]

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" <na**@notreal.add> wrote in message
news:O9********************@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

[Pippen]

"John Bell" <jb************@hotmail.com> wrote in message
news:42***********************@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" <na**@notreal.add> wrote in message
news:O9********************@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