Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find Max number in a table

Status
Not open for further replies.

LittleNick

Technical User
Jun 26, 2009
55
US
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
 
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
 
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
 
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
 
Hi,

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

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top