min or max over multiple columns

SQL Apprentice Question
A short question about min and max functions. Let's say I have the following
table:


contract sales_2005 sales_2006
1234 100 120
5678 870 560


Now, I want to build a query that gives the following result:


contract maximum_sales
1234 120
5678 870


So I want to get the maximum yearly sales per contract over the years 2005
and 2006. A normal max-function won't work because it only calculates the
maximum row in a given column. Is there a function that does what I'm looking
for? Also to get the minimum yearly sales per contract.


Of course, it can be solved with a greater than/less than construction but
I'm looking for a more efficient solution.


Thanks in advance!



Celko Answers
This is a cross tabs and not a table. You have split an attribute,
time, over two columns.

In Oracle, you would use GREATEST (sales_2005, sales_2006), but we have
to do more work:


SELECT X,contract_nbr, MAX(X.sales_annual)
FROM (SELECT S1.contract_nbr, S1.sales_2005
FROM SalesCrossTab AS S1
UNION ALL
SELECT S2.contract_nbr, S2.sales_2006
FROM SalesCrossTab AS S2)
AS X(contract_nbr, sales_annual)
GROUP BY X,contract_nbr;