Find Max number in a table

[LittleNick]

Hi everyone,
I have a table with 4 columns, namely customer,price1, price2, price3. how can I find the maximum price for each custom. Here is an example of my table:

Customer price1 price2 price3
john $3.00 $2.00 $1.00
john $6.00 $3.00 $4.00
Mary $2.00 $5.00 $3.00

The result desired is John $6.00 and Mary is $5.00

Thanks for helps

 

[snitin78]

Take a look here:

http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns

Nitin

 

[simian336]

I think this should work

with test as
(
select Customer, max(price1) as maxval
from dbo.table
group by Customer
union
select Customer, max(price2) as maxval
from dbo.table
group by Customer
union
select Customer, max(price3) as maxval
from dbo.table
group by Customer
)
select Customer, max(maxval) finalval
from test
group by Customer

Simi

 

[LittleNick]

Thanks Nitin and Simi for your replies. I have been assigned to another project with higher priority, but I will come back on this problem. I will save it into my archive. Thanks again

 

[NiceArms]

I saw this question a while ago and it really got me thinking....don't know why, it just did..... and I have been playing with some ideas. the following does work but is not very scalable!


create table #tmpt1(
customer varchar(24) null,
price1 decimal(18,2) null,
price2 decimal(18,2) null,
price3 decimal(18,2) null,
) on [primary]

insert into #tmpt1
(customer, price1, price2, price3)
select 'john',3.00, 2.00, 1.00
insert into #tmpt1
(customer, price1, price2, price3)
select 'john',6.00, 3.00, 4.00
insert into #tmpt1
(customer, price1, price2, price3)
select 'mary',2.00, 5.00, 3.00

select customer,
case when max(price1) > Max(Price2) and max(price1) > max(price3) then Max(Price1) else
case when Max(Price2) > Max(Price1) and Max(Price2) > Max(Price3) then Max(Price2) else
case when Max(Price3) > Max(Price1) and Max(Price3) > Max(Price2) then Max(Price3)
end
end
end
from #tmpt1
group by customer

drop table #tmpt1

I am thinking of working along the lines of populating a table based on whether the value is GT the current value. I will post when i figure it out/ have the time.

/Nice

 

[markros]

Check the last response (a link to an article) in this thread

http://social.msdn.microsoft.com/Fo...l/thread/d4b4d28e-505e-49a6-b56d-8eab2b9c4020

PluralSight Learning Library

 

[RyanEK]

Hi,

You could just find the max of an unpivoted table...

declare @tmp table (customer varchar(10), price1 int, price2 int, price3 int)
insert into @tmp values ('john',3,25,1)
insert into @tmp values ('john',6,3,4)
insert into @tmp values ('mary',2,5,3)

SELECT customer, max(price)
FROM @tmp
unpivot (price for best in (price1, price2, price3)) p
group by customer

Ryan