Re: GROUP BY question

Heinz Huber wrote:
>
> Hi,
>
> Dan Star wrote:
> >
> > Carlos,
> >
> > I had to remove CustPN from the SELECT clause in the sub query and then
> > it worked in Access97.
> >
> > Thanks,
> > Dan
> >
> > Carlos Bromanski wrote:
> > >
> > > SELECT Whse, CustPN, Price
> > > FROM ThatTable AS A
> > > WHERE Price = (
> > > SELECT max(Price), CustPN
> > > FROM ThatTable AS B
> > > WHERE B.CustPN = A.CustPN
> > > GROUP BY CustPN)
>
> Keep in mind though, that this query might give you more than one row
> per CustPN if there is more than one row with the maximum Price.
>

Good point. You may want to see all the warehouses where the maximum price is or you can add an outer GROUP BY to just pull one of the max prices and the warehouse it is in. I have learned much on using sub queries.

Thanks,
Dan Received on Fri Apr 20 2001 - 19:39:19 CEST