how to get max value of multiple rows ???

[jkshetty]

eg :

product amt1 amt2 amt3 amt4

a 100 200 150 170
b 120 33 54 94

the max amt is : "200" for product a
the max amt is : "120" for product a



pls get me SQL Query.

[amitpatel66]

eg :

product amt1 amt2 amt3 amt4

a 100 200 150 170
b 120 33 54 94

the max amt is : "200" for product a
the max amt is : "120" for product a



pls get me SQL Query.

Try this query:

[code=sql]
SELECT MAX(x),product FROM
(SELECT product p,amt1 x from table1
UNION
SELECT product, pamt2 x from table1
UNION
SELECT product, pamt3 x from table1
UNION
SELECT product, pamt4 x from table1) y
GROUP BY product
[/code]