return highest value in recordset

Discussion in 'ASP General' started by shank, Jun 25, 2004.

  1. shank

    shank Guest

    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
    shank, Jun 25, 2004
    #1
    1. Advertising

  2. shank

    Maarten Guest

    SELECT TOP 10 ....
    Maarten, Jun 25, 2004
    #2
    1. Advertising

  3. shank

    Evertjan. Guest

    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)
    Evertjan., Jun 25, 2004
    #3
  4. shank

    Al Reid Guest

    "shank" <> wrote in message news:OZW%...
    > 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.
    Al Reid, Jun 25, 2004
    #4
  5. 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.
    Dave Anderson, Jun 25, 2004
    #5
  6. 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.
    Dave Anderson, Jun 25, 2004
    #6
  7. shank

    shank Guest

    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...
    shank, Jun 25, 2004
    #7
  8. Aaron [SQL Server MVP], Jun 25, 2004
    #8
  9. shank

    shank Guest

    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]" <> wrote in message
    news:%...
    > > 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.)
    >
    >
    shank, Jun 25, 2004
    #9
  10. 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"
    Bob Barrows [MVP], Jun 26, 2004
    #10
  11. shank

    shank Guest

    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]" <> wrote in message
    news:...
    > 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"
    >
    >
    shank, Jun 26, 2004
    #11
  12. "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.
    Dave Anderson, Jun 26, 2004
    #12
  13. > 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
    Aaron [SQL Server MVP], Jun 26, 2004
    #13
  14. "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.
    Dave Anderson, Jun 26, 2004
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sparky Arbuckle

    Return Highest Record (SQL - Access)

    Sparky Arbuckle, Aug 17, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    3,123
    Paul Clement
    Aug 18, 2005
  2. Stone
    Replies:
    0
    Views:
    360
    Stone
    Nov 24, 2004
  3. NoKetch

    Rounding to next highest number?

    NoKetch, Dec 15, 2003, in forum: C Programming
    Replies:
    7
    Views:
    580
    Mark McIntyre
    Dec 15, 2003
  4. Greenhorn
    Replies:
    15
    Views:
    812
    Keith Thompson
    Mar 6, 2005
  5. Hung Huynh
    Replies:
    8
    Views:
    300
    Bob Barrows
    Sep 24, 2003
Loading...

Share This Page