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

## No comments:

Post a Comment