Re: Problem with the group statement...

Maybe something like this?

select TID, PN, PP, ED from x as X1
where not exists (
  select * from x
  where (TID, PP, ED) > (X1.TID,X1.PP,X1.ED)   and PN = X1.PN
)

Steve Kass
Drew University

Volker Hetzer wrote:

> "James Chapman" <Jim.Chapman_at_nospam.elsegundoca.ncr.com> wrote in message news:3d333d26$1_at_rpc1284.daytonoh.ncr.com...
> > I believe your select statement is not correct standard SQL.
> Shouldn't the parser haverejected it in that case?
> Then again, I use mysql and maybe it's less stringent...
>
> > All columns in
> > the select list must be either aggregate expressions or columns specified in
> > the GROUP BY clause.
> Ok, here's an example of what I'm trying to accomplish:
>
> Assume a table x of the form
> TID int auto_increment, --Transaction ID's
> PN int, --part numbers
> PP double, --part price
> ED char(25) --name of the editor that entered the part
>
> Editors enter new prices for each part each month.
>
> Now, I'd like to see, for each part, the price and editor who entered
> it latest (i.e. with the highest TID).
>
> I would write:
> select max(TID), PN, PP,ED from x group by PN;
>
> Now, PP and ED are not in aggregate functions and not
> in the group clause.
> Is this statement wrong and how could I achieve that I'm
> trying to?
> and is there a way to return not max(TID) but all the rest?
> Is it guaranteed that PN,PP and ED are all from the same
> row, even if there are (theoretically) several TID's that
> satisfy max(TID) for a given PN?
>
> > The result you reported seems to be as if your SQL
> > statement had been written thus:
> > select a, min(b) from x group by a;
>
> Yeah, looks that way.
>
> Thanks a lot!
> Volker
Received on Tue Jul 16 2002 - 18:56:21 CEST