Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: Yehuda Epstein
  Where is Yehuda Epstein?
 Monsey
 New York - United States
 Yehuda Epstein
 Tags
Subject: RE: Calcultions
Thread ID: 268787 Message ID: 268797 # Views: 31 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 5:59:14 PM         
   


select temp.product,temp.maxamount,mytable.location from (select product,max(amount) as maxamount from MyTable group by product) temp left join mytable on mytable.amount=temp.maxamount and mytable.product=temp.product



Regards
Tushar

> Thanks Cetin,
> the query:
>
> select product, location, max(amount) as amount ;
> from crsIntermediate ;
> group by product, location ;
> into cursor crsFinal
> 

> groups by product and location. I need to group it by product only, since I am only interested in the locations that have the max amount for each product and not the max amount for the combination of product & location.
> Example:
> Mytable has:
> product Amount Quality Location
> 1 3 1 221
> 1 5 1 224
> 1 7 2 221
> 2 6 2 223
> 2 5 2 220
> 2 9 1 221
> I would like the cursor crsFinal to display:
> product Amount Location
> 1 10 221
> 2 9 221
> Thanks again,
> Yehuda.
>
> >
> > Wish you provided some sample data using SampleDataToText.prg here at FAQ section. By calculate i think you mean sum() per location?
> >
> >
select product, location, sum(amount) as amount ;
> > from myTable ;
> > group by product, location ;
> > into cursor crsIntermediate
> > 
> > select product, location, max(amount) as amount ;
> > from crsIntermediate ;
> > group by product, location ;
> > into cursor crsFinal
> > 
> > browse
> > 

> >
> > Cetin Basoz
> >
> > .Net has got better.Think about moving - check my blog:
> > Blog (main)
> > Blog (mirror)

ENTIRE THREAD

Calcultions Posted by Yehuda Epstein @ 7/14/2010 5:01:46 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 5:08:38 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 5:48:45 PM
RE: Calcultions Posted by tushar @ 7/14/2010 5:59:14 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 6:10:43 PM
RE: Calcultions Posted by tushar @ 7/14/2010 7:19:10 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 6:24:09 PM
RE: Calcultions Posted by Samir H. @ 7/14/2010 6:48:57 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 7:11:38 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 9:50:36 PM
RE: Calcultions Posted by Samir H. @ 7/15/2010 3:54:29 AM
RE: Calcultions Posted by Cetin Basoz @ 7/15/2010 12:03:52 PM
RE: Calcultions Posted by Samir H. @ 7/15/2010 1:04:35 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 6:49:47 PM
RE: Calcultions Posted by Samir H. @ 7/14/2010 6:58:04 PM