Re: Problem with the group statement...

"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 - 12:39:52 CEST