I want to use Max to find the largest value in a row.  That works with the 

following:

sqltext="SELECT  Max(workouts2.Weight) AS MaxOfWeight FROM workouts2; "



But now I want to know about that record such as the member id for which 

the max weight was achieved.  Logically (although not syntatically 

correct) I want to do the following:



sqltext="SELECT workouts2.[member id], Max(workouts2.Weight) AS 

MaxOfWeight FROM workouts2; "



I want this to allow me to show the member id.  However, since there is 

no 'group by' I get an error.  I don't want to group by.  I just want to 

get more data out of the row in which the max weight was found.  How do I 

access the data in the row?  Do I need a separate query?

thanks,

Gary

I'll have a crack at this, typically I don't use MAX in the way that you 

have used it here, so if my understanding is a bit off the rails, please 

excuse me.



Min and Max are known as "Group functions", in and effort to compare 

apples with apples.  Because MAX is a group function, you need to include 

a group by clause <B>when you also select data from the same row as the 

maximum value.</B>



Because you want other details from the maximum line, you are best off 

selecting all the fields you need (you can't select star '*' in a group 

by), grouping them logically, then sorting them by the maximum field 

(descending).  



Here comes the key, in your recordset, only proceed to the first record, 

which contains the maximum value for your selection criteria.  You know it 

contains the maximum value, because you have already sorted the recordset.



All this will work well, provided you include in your WHERE clause the id 

or name of the weightlifter.  I figure in your example, it would be better 

to use WHERE instead of HAVING. 



You don't need a multiple select statement. 



Regards



Paul 

I've had some inspiration and felt it necessary to include another way to 

solve your problem, with an SQL statement like...



select * from weightlog

where sizet  = (Select MAX(sizet) FROM weightlog WHERE namet= "taylor")

AND namet  = "taylor"



You'll notice that there is no need to include a GROUP BY clause in the 

above sub-query because I am only selecting one field in the sub select 

which is the MAX group function itself, this is traditionally the more 

accepted way to use MAX (at least where I come from anyway)



Secondly it is important to include the name (or id) of the weightlifter 

twice, once to select his/her highest weight, and a second time to grab 

all the data about that row. 



You will only get back one result, except in the circumstance when the 

weightlifter has lifted the same maximum weight more then once. 



To limit this possibility, I personally would also run another sub-select 

on the date, similar to how I have done it above.  That way, you would get 

only one maximum weight, no matter how many times he/she hit that weight, 

and the details would reflect the latest (or maximum) date. 



In the end, you have no limit of options. 



Regards



Paul

Paul,



I just wanted to say thanks.  You got me thinking about good 

alternatives.  I suspect that the max function is faster if I could figure 

out how to use it.  But I'll settle for the alternatives you presented.