Re: Max of different columns

On Thu, 29 Dec 2005 23:11:02 -0800, Joe Thompson wrote:

>Hi,
>
>Say I have a table like this
>
>Name F1 F2 F3
>Joe 3 4 2
>Bill 1 3 7
>Bob 4 4 2
>
>How can I write a query to select the name and max of F1, F2, F3 so my
>results are
>Joe 4
>Bill 7
>Bob 4
>
>Thank you,
>Joe

Hi Joe,

There are two ways, but none of them is easy:

SELECT Name,
CASE WHEN F1 >= F2 AND F1 >= F3 THEN F1
WHEN F2 >= F3 THEN F2
ELSE F3
END AS MaxOfF1F2F3
FROM YourTable


SELECT Name,
MAX(Number)
FROM (SELECT Name, F1 AS Number
FROM YourTable
UNION ALL
SELECT Name, F2 AS Number
FROM YourTable
UNION ALL
SELECT Name, F3 AS Number
FROM YourTable) AS Derived
GROUP BY Name

(both queries untested - see www.aspfaq.com/5006 if you prefer a tested
reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.