Finding the maximum value in a recordset column

[Roger Withnell]

How do I find the maximum value of a recordset column? I'd rather do it
this way than open a new recordset with Max(column).

Thanks in anticipation.

Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

[Bob Barrows [MVP]]

Roger Withnell wrote:

How do I find the maximum value of a recordset column? I'd rather do
it this way than open a new recordset with Max(column).

Thanks in anticipation.

My preference would be to let the database (whatever it is) do this. If
using sql server, I would write a stored procedure that returned the
records, as well as returning the max(column) in an output parameter. If
Access, I would still make a second trip to the database.

However, what you can do is:

1. Optional but highly recommended: disconnect the recordset by setting its
ActiveConnectio n property to Nothing. This will allow you to close and
destroy your Connection object, freeing it up for another thread to use

2. Use the recordset's Sort property to sort by the column you wish to find
the max value of.

3. Move to the first or last record (depending on the sort direction you
used) and read the value.

HTH,
Bob barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bullschmidt]

If you're looping through the recordset with your ASP code and then
showing the max after all that I'd suggest something similar to a
running sum ASP variable. But of course this would check each value and
only make a change if it's larger than the current value:

If objRS("TheValue ") > MyMaxVariable Then
MyMaxVariable = objRS("TheValue ")
End If

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

<<
How do I find the maximum value of a recordset column? I'd rather do it
this way than open a new recordset with Max(column).

Thanks in anticipation.

*** Sent via Developersdex http://www.developersdex.com ***