469,625 Members | 1,078 Online

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

-p

Jul 23 '05 #1
2 1386
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
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

-p

Jul 23 '05 #2

"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
It is also useful to post your current attempts at solving the problem.

John
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

-p

Thanks for the help.

-p
Jul 23 '05 #3