By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,068 Members | 867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,068 IT Pros & Developers. It's quick & easy.

return highest value in recordset

P: n/a
How do you return the highest value in a recordset of maybe 100 records?
Is it necessary to run 2 recordsets?
I was hoping it was as simple as Max([Price]), but no luck.

thanks
Jul 19 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
SELECT TOP 10 ....
Jul 19 '05 #2

P: n/a
shank wrote on 25 jun 2004 in microsoft.public.inetserver.asp.general:
How do you return the highest value in a recordset of maybe 100 records?
Is it necessary to run 2 recordsets?
I was hoping it was as simple as Max([Price]), but no luck.


if you mean a database:

Select top 1 myField, myOtherField from myTable order by myField Desc

or

Select max(myField) as higest from myTable

(if you only want that value)
Not tested, so I could be completely wrong.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Jul 19 '05 #3

P: n/a

"shank" <sh***@tampabay.rr.com> wrote in message news:OZ****************@tk2msftngp13.phx.gbl...
How do you return the highest value in a recordset of maybe 100 records?
Is it necessary to run 2 recordsets?
I was hoping it was as simple as Max([Price]), but no luck.

thanks


If you want the entire record try:

SELECT * FROM tbl WHERE fld = (SELECT MAX(fld) FROM tbl)

Better yet, only select the fields you really need.
Jul 19 '05 #4

P: n/a
shank wrote:
How do you return the highest value in a recordset of maybe 100
records? Is it necessary to run 2 recordsets?
I was hoping it was as simple as Max([Price]), but no luck.


Define "highest value" in something that is usually multi-dimensional, with
multiple data formats.

There are probably several solutions, and you don't really provide enough
detail for me to recommend one over another. Here's one:

While NOT RS.EOF
If RS.Fields("Price").Value > MaxValue Then
MaxValue = RS.Fields("Price").Value
End If

...other processing...

Call RS.MoveNext()
Wend

Even better, if you have used GetRows, you don't have to worry about cursor
types, and you can do all kinds of things to the data:

Rows = RS.GetRows(), priceColumn = [ integer: depends on your query ]
For i = 0 To UBound(Rows,2)
If Rows(priceColumn,i) > MaxValue Then
MaxValue = Rows(priceColumn,i)
End If
Next

Note that this can be done at any time, since it's in an array. No need to
worry about cursor types, and no need to leave connections or recordsets
open. On the other hand...

If your query is ordered on Price, just grab the first (DESC ordered query)
or last (ASC ordered one) value in the recordset.

If you don't mind a second recordset, you can use this approach:

SELECT TOP 1 Price
FROM MyTable
WHERE [ same conditions as earlier query ]
ORDER BY Price DESC

As I said, options abound.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Jul 19 '05 #5

P: n/a
Al Reid wrote:

SELECT * FROM tbl WHERE fld = (SELECT MAX(fld) FROM tbl)


That reminds me -- one can always return it as a column in the recordset:

DECLARE @MaxPrice DECIMAL(9,2)
SELECT @MaxPrice = MAX(Price) FROM myTable

SELECT *, @MaxPrice AS MaxPrice
FROM myTable
WHERE [ your conditions ]
MaxPrice will be in every row. Not the most efficient way, but not
necessarily a bad one.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Jul 19 '05 #6

P: n/a
I wanted to try the GetRows() method and I'm not having much luck. Actually,
"the page cannot be displayed".
I assumed you wanted some integer like 100 for [ integer: depends on your
query ] ... correct?
Where am I going wrong?
thanks
<%
Rows = rsShowrecords.GetRows(), Price = 100
For i = 0 To UBound(Rows,2)
If Rows(Price,i) > MaxValue Then
MaxValue = Rows(Price,i)
End If
Next
%>

Highest Price: <%=MaxValue%>
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= =
Even better, if you have used GetRows, you don't have to worry about cursor types, and you can do all kinds of things to the data:

Rows = RS.GetRows(), priceColumn = [ integer: depends on your query ]
For i = 0 To UBound(Rows,2)
If Rows(priceColumn,i) > MaxValue Then
MaxValue = Rows(priceColumn,i)
End If
Next

Note that this can be done at any time, since it's in an array. No need to
worry about cursor types, and no need to leave connections or recordsets
open. On the other hand...

Jul 19 '05 #7

P: n/a
> I wanted to try the GetRows() method and I'm not having much luck.
Actually,
"the page cannot be displayed".


http://www.aspfaq.com/2109

--
http://www.aspfaq.com/
(Reverse address to reply.)
Jul 19 '05 #8

P: n/a
I had Show friendly errors unchecked before. I'm on a shared server and I
contacted my host. They cannot change anything that would reflect a more
descriptive error. So I'm back to "what's wrong with this?"
My recordset is: rsShowrecords
The price column is: Price
I'm placing the below code after the recordset.
What am I missing?
thanks!

<%
Rows = rsShowrecords.GetRows(), Price = 100
For i = 0 To UBound(Rows,2)
If Rows(Price,i) > MaxValue Then
MaxValue = Rows(Price,i)
End If
Next
%>

Highest Price: <%=MaxValue%>
--------------------------------------------------------
--------------------------------------------------------

"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
I wanted to try the GetRows() method and I'm not having much luck.

Actually,
"the page cannot be displayed".


http://www.aspfaq.com/2109

--
http://www.aspfaq.com/
(Reverse address to reply.)

Jul 19 '05 #9

P: n/a
shank wrote:
<%
Rows = rsShowrecords.GetRows(), Price = 100


This is a single line of code???

Why would you think this could work? You're trying to do two things in a
single statement??
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #10

P: n/a
That was the code sample I was given.

Rows = RS.GetRows(), priceColumn = [ integer: depends on your query ]
For i = 0 To UBound(Rows,2)
If Rows(priceColumn,i) > MaxValue Then
MaxValue = Rows(priceColumn,i)
End If
Next

This doesn't work either ....
<%
Rows = rsShowrecords.GetRows(), Price
For i = 0 To UBound(Rows,2)
If Rows(Price,i) > MaxValue Then
MaxValue = Rows(Price,i)
End If
Next
%>

thanks

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:e9*************@TK2MSFTNGP11.phx.gbl...
shank wrote:
<%
Rows = rsShowrecords.GetRows(), Price = 100


This is a single line of code???

Why would you think this could work? You're trying to do two things in a
single statement??
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 19 '05 #11

P: n/a
"shank" wrote:
Why would you think this could work? You're trying to do
two things in a single statement??


That was the code sample I was given.


I'll take the blame, but you should take responsibility.

I write ASP primarily in JScript, so my VBScript examples should be taken as
pseudo-code. Over time, I have managed to remember that I cannot declare a
variable and assign it in the same VBScript statement. Apparently, I have
not learned that each assignment requires its own statement. This is
acceptable JScript, FWIW:

var a = Request.Form("a").Item, b = a.length, i = j = k = 0

The equivalent VBScript?

Dim a, b, i, j, k
a = Request.Form("a").Item
b = Len(a)
i = 0
j = 0
k = 0

See why I sometimes forget a conversion rule?

It nonetheless is your responsibility to debug. There are any number of
steps you could take to narrow the error down before posting a "didn't work"
response.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Jul 19 '05 #12

P: n/a
> var a = Request.Form("a").Item, b = a.length, i = j = k = 0

The equivalent VBScript?

Dim a, b, i, j, k
a = Request.Form("a").Item
b = Len(a)
i = 0
j = 0
k = 0


Well, you can drop the dim, and say.

a = Request.Form("a").Item: b = Len(a): i = 0: j = 0: k = 0

Juet being devil's advocate. ;-)

A
Jul 19 '05 #13

P: n/a
"Aaron [SQL Server MVP]" wrote:
var a = Request.Form("a").Item, b = a.length, i = j = k = 0

The equivalent VBScript?

Dim a, b, i, j, k
a = Request.Form("a").Item
b = Len(a)
i = 0
j = 0
k = 0


Well, you can drop the dim, and say.

a = Request.Form("a").Item: b = Len(a): i = 0: j = 0: k = 0


Which I more or less knew, but forgot when posting the original. This has
little effect in the global namespace, but if you want variables local to
your function/Function/Sub, you usually can't avoid var/Dim statements.

And you have to admit, i=j=k=0 is impossible to misinterpret, while being at
least as pleasing to the eye than i=0:j=0:k=0.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Jul 19 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.