joe thompsonn&v&r

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

hugo kornelis

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 http://www.aspfaq.com/5006 if you prefer a tested
reply)

Best, Hugo
--

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

joe thompsonn&v&r

Thanks Hugo - I'll give them a try.

Joe