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

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

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:

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)

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.

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:

You're probably right. I just could not get a subquery with a union to work in my antiquated version of Access.

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!