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,

Thanks Hugo - I'll give them a try.

Joe

[quoted text, click to view]

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
--