Results 1 to 15 of 15

Thread: Max function

  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: Max function

    I have some problem about sql Again!!!
    I have the table name aa
    id Pricecom1 Pricecom2 Pricecom3 Pricecom4
    1 12 54 21 33
    2 32 12 31 45
    3 33 11 12 22
    4 54 44 43 13
    5 11 6 100 10

    I want to find the maximum number of the button of every column. Form this example it should return 100

    I tried
    sql="select top 1 * from aaa where (select max(com1,com2,com3)as maxvalue) order by id desc"

    but it doesn't work
    Last edited by gop373; 09-27-04 at 09:29.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select max(daPrice)
      from (
           select Pricecom1 as daPrice from aa
           union
           select Pricecom2 from aa
           union
           select Pricecom3 from aa
           union
           select Pricecom4 from aa
           ) as NormalizedTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    77
    It doesn't work

    <!--#include file="connect.asp"-->
    <%
    sql="select max(daPrice)from (select com1 as daPrice from aaa union select com2 from aaa union select com3 from aaa union select com4 from aaa ) as NormalizedTable"
    set rst=server.createobject("adodb.recordset")
    rst.open sql,conn,1,3
    if not rst.eof then
    response.write rst("com1")&"<br>"
    response.write rst("com2")&"<br>"
    response.write rst("com3")&"<br>"
    response.write rst("NormalizedTable")&"<br>"
    end if
    %>

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    max can also be used as a scalar function(max(1,2,3)=3), so apply it to the result for each column:

    select select max(max(com1),max(com2),max(com3)) from aaa

  5. #5
    Join Date
    Aug 2004
    Posts
    77
    Urquel , I got the message

    Microsoft JET Database Engine error '80040e14'

    Wrong number of arguments used with function in query expression 'max(max(com1),max(com2),max(com3))'.
    <!--#include file="connect.asp"-->
    <%
    sql="select max(max(com1),max(com2),max(com3),max(com4)) ) as maxvalue1 from aaa "
    set rst=server.createobject("adodb.recordset")
    rst.open sql,conn,1,3
    if not rst.eof then
    response.write rst("maxvalue1")&"<br>"
    end if
    %>


    Note:: I use Microsoft Access 2000
    Last edited by gop373; 09-27-04 at 11:07.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gop373
    Note:: I use Microsoft Access 2000
    this should either have been mentioned at the outset, or you should have posted in the access forum

    of course, i knew it was either access or sql server by your mention of TOP

    but hey, my solution should have worked anyway

    when you say "doesn't work" what do you mean -- ran but got wrong results? ran but got 0 results? didn't run, got a syntax error? what's the error?

    while you're pondering how to answer, might i suggest a workaround

    take the union query in the subselect, and save it as SavedQuery

    then run this:
    Code:
    select max(daPrice) from SavedQuery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    77
    Hi r397 It didn't run. I got "The page cannot be displayed"
    Now I got a litter bit idea so I tried to something like this

    sql="select Max("com1")as max1 from aaa"
    set rst=server.createobject("adodb.recordset")
    rst.open sql,conn,1,3
    sql="select Max("com2")as max2 from aaa"
    set rst2=server.createobject("adodb.recordset")
    rst2.open sql,conn,1,3
    sql="select Max("com3")as max3 from aaa"
    set rst3=server.createobject("adodb.recordset")
    rst3.open sql,conn,1,3
    sql="select Max("com4")as max4 from aaa"
    set rst4=server.createobject("adodb.recordset")
    rst4.open sql,conn,1,3

    dim maxvalue
    if rst("max1") >= rst("max2") and rst("max1") >= rst("max3") and rst("max1") >= rst("max4"))
    then maxvalue=rst("max1")
    else if rst("max2") >= rst("max1") and rst("max2") >= rst("max3") and rst("max2") >= rst("max4"))
    then maxvalue=rst("max2")
    else if rst("max3") >= rst("max1") and rst("max3") >= rst("max2") and rst("max3") >= rst("max4"))
    then maxvalue=rst("max3")
    else maxvalue=rst("max4")
    end if


    BUT BUT......Don't know what to do next (still try)
    Last edited by gop373; 09-27-04 at 12:01.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you may want to ask these questions in the ASP forum, because it is now likely a programming issue, not an sql problem

    did you save the subquery as a saved query like i suggested?

    i am certain the query of the saved query will work -- i tested it in access 97
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2004
    Posts
    77
    Ok I will try again. Sometime it is diffecult to know the problem is program or SQL. Thanks you very much.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gop373
    Sometime it is diffecult to know the problem is program or SQL.
    understood, but with access, which is a desktop database, you really should always test your sql in the access query window first

    this is guaranteed to ensure that you eliminate sql errors before you even start programming
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2004
    Posts
    330
    OH ACCESS!!(subquerys don't work)

    You will need 2 queries, the first will union the 3 columns, the second will select a max from the first query.

    Query1:
    select aaa.Pricecom1 as Price from aaa
    union
    select aaa.Pricecom2 as Price from aaa
    union
    select aaa.Pricecom3 as Price from aaa
    union
    select aaa.Pricecom4 as Price from aaa;

    Qurey2:
    select max(Query1.price) as MaxPrice from Query1;

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, urquel, subqueries do work in access, you're probably thinking of mysql

    your solution is exactly the same as the one i suggested in post #6

    FYI here's the slight change in syntax to make my query in post #2 work:
    Code:
    select max(daPrice)
      from [
           select Pricecom1 as daPrice from aa
           union
           select Pricecom2 from aa
           union
           select Pricecom3 from aa
           union
           select Pricecom4 from aa
           ]. as NormalizedTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2004
    Posts
    330
    You're probably right. I just could not get a subquery with a union to work in my antiquated version of Access.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how antiquated? the sql i showed in post #12 works in access 97
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2004
    Posts
    330
    Access 97. I used parenthesis instead of the sqaure brackets and got an error. Go figure!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •