469,625 Members | 1,078 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

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.

-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
"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


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
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Solel Software | last post: by
4 posts views Thread by Aaron Smith | last post: by
6 posts views Thread by bobueland | last post: by
67 posts views Thread by PC Datasheet | last post: by
2 posts views Thread by =?Utf-8?B?UmljaA==?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.