min or max over multiple columns

Hi all,
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 lookin
g
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!
regards,
MaartenWhy is your data not properly normalized?
You can normalize it for the purpose of the aggregation by using a UNION
query (untested, since you haven't provided proper DDL):
select sales_2005 as Sales
,2005 as Year
from <table>
union all
select sales_2006 as Sales
,2006 as Year
from <table>
...then use this as a derived table and apply the aggregation:
select max(Normalized.Sales) as MaxSales
,min(Normalized.Sales) as MinSales
from (
select sales_2005 as Sales
,2005 as Year
from <table>
union all
select sales_2006 as Sales
,2006 as Year
from <table>
) Normalized
Is this what you need?
ML
http://milambda.blogspot.com/|||I would use a greater than/less than for this.
Something like (you would have to add Coalesce or IsNull function calls if
either sales_2005 or sales_2006 can be NULL):
Select contract,
Max(Case When sales_2005 > sales_2006 Then sales_2005 Else sales_2006 End)
As maximum_sales
From ...
Group By contract
Order By contract
If you absolutely want to avoid greater than/less than, you could do:
Select contract,
Max(sales) As maximum_sales
From (Select contract, sales_2005 As sales From ...
Union All Select contract, sales_2006 As sales From ...) x
Group By contract
Order By contract
but that would probably be less efficient than the first query.
Tom
"maarten_dtg" <u23468@.uwe> wrote in message news:625ad6dff69a4@.uwe...
> Hi all,
> 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!
> regards,
> Maarten|||See the execution plan for the three "select" statements as a batch.
create table dbo.t1 (
contract int not null primary key,
sales_2005 money not null,
sales_2006 money not null
)
go
insert into dbo.t1
select
*
from
(
select 1234 as contract, 100 as sales_2005, 120 as sales_2006
union all
select 5678, 870, 560
) as t2
go
select
contract,
case when sales_2005 > sales_2006 then sales_2005 else sales_2006 end as
max_sales
from
dbo.t1
select
contract,
max(sales) as max_sales
from
(
select contract, sales_2005 as sales from dbo.t1
union all
select contract, sales_2006 as sales from dbo.t1
) as t2
group by
contract
select
dbo.t1.contract,
max(case when t2.c1 = 1 then dbo.t1.sales_2005 else dbo.t1.sales_2006 end)
as max_sales
from
dbo.t1
cross join
(
select 1 as c1 union all select 2
) as t2
group by
dbo.t1.contract
go
drop table dbo.t1
go
AMB
"maarten_dtg" wrote:

> Hi all,
> A short question about min and max functions. Let's say I have the followi
ng
> 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 look
ing
> 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!
> regards,
> Maarten
>|||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;