MAX Value over multiple columns
706366Jul 9 2010 — edited Feb 10 2011hi,
in my query i need the MAX value of 3 different columns.
example: column 1 = 10, column 2 = 20, column 3 = 30 > output should be 30.
i need this MAX value to sort the list by it.
this is how i tried to do it:
================================
select t1.id,
(
SELECT MAX(SUB.value)
FROM
(
SELECT
CASE TO_CHAR(ROWNUM)
WHEN TO_CHAR(1) THEN (SELECT height FROM myTable WHERE id = t1.id)
WHEN TO_CHAR(2) THEN (SELECT width FROM myTable WHERE id = t1.id)
WHEN TO_CHAR(3) THEN (SELECT depth FROM myTable WHERE id = t1.id)
END AS value
FROM dual t1
CONNECT BY ROWNUM <= 3
) SUB
) AS sort
from myTable t1
order by sort
================================
this would work fine if i could reach t1 within the subselect ( ... WHERE id = t1.id ...).
unfortunately i cant access t1.id at this point.
does anybody have some advice for me?
thanks a lot!