472,807 Members | 2,620 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 1456
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

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 3 by: Jonathan | last post by: Hi all! For a match schedule I would like to find all possible combinations of teams playing home and away (without teams playing to themselves of course). I now the simple version works... 2 by: Daniel | last post by: I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data... 1 by: Just Me | last post by: Table A contains a list of colors (example red, blue, green) Table B contains a variation of the colors in Table A (example light-red, medium-red, dark-red) To add a color variation to Table... 3 by: Solel Software | last post by: Hello, I have a basic question. I have a DataTable of information without a database store (it's only in memory). I am looking to somehow query the DataTable to find out which row(s) satisfy... 4 by: Aaron Smith | last post by: Dim dv As DataView = New DataView(FacilitiesDS1.Facilities, "", "ID ASC", DataViewRowState.CurrentRows) Dim iPos As Integer = dv.Find(dr.Item("ID")) Me.BindingContext(FacilitiesDS1,... 6 by: bobueland | last post by: The module string has a function called translate. I tried to find the source code for that function. In: C:\Python24\Lib there is one file called string.py I open it and it says 67 by: PC Datasheet | last post by: Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ... 0 by: Max | last post by: If you don't want to read the drivel in my main message body here is the question at the end. How do you find a string in a column and make the BindingNavigator Navigate to the correct row?? ... 2 by: =?Utf-8?B?UmljaA==?= | last post by: Greetings, I need to locate a row (or see if it exists) in a table contained in a dataset. The DataView.Find method seems to work OK for one criteria but I need to use 2 criterias. Is there... 7 by: john | last post by: In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent... 2 by: isladogs | last post by: The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central... 0 by: erikbower65 | last post by: Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on... 0 by: kcodez | last post by: As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it... 0 by: Taofi | last post by: I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ... 0 by: Rina0 | last post by: I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in... 5 by: DJRhino | last post by: Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or... 0 by: lllomh | last post by: How does React native implement an English player? 0 by: Mushico | last post by: How to calculate date of retirement from date of birth 2 by: DJRhino | last post by: Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...