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 workselect 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|||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
%>|||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|||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|||Note:: I use Microsoft Access 2000this 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:select max(daPrice) from SavedQuery|||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) :p|||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|||Ok I will try again. Sometime it is diffecult to know the problem is program or SQL. Thanks you very much.|||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|||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;|||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: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|||You're probably right. I just could not get a subquery with a union to work in my antiquated version of Access. :D|||how antiquated? the sql i showed in post #12 works in access 97|||Access 97. I used parenthesis instead of the sqaure brackets and got an error. Go figure!sql