Why is this an SQL syntax error?

Informix 7.x select p_name,rowid,max(finish_dt) from p_status where id=10 group by 1,2 gives rise to a syntax error why? Sent via Deja.com http://www.deja.com/ Before you buy.

strangiato@my-deja.com wrote: > > Informix 7.x > > select p_name,rowid,max(finish_dt) from p_status where id=10 group by > 1,2> > gives rise to a syntax error > > why? > > Sent via Deja.com http://www.deja.com/ > Before you buy. Is p_name a column name or a program variable ?? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

It's a column name. I suspect there is a problem with the rowid?? In article <390F01AD.30A7E5DD@bellsouth.net>, "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: > strangiato@my-deja.com wrote: > > > > Informix 7.x > > > > select p_name,rowid,max(finish_dt) from p_status where id=10 groupby > > 1,2 > > > > gives rise to a syntax error > > > > why? > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > Is p_name a column name or a program variable ?? > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */ > Sent via Deja.com http://www.deja.com/ Before you buy.

strangiato@my-deja.com wrote: > > It's a column name. I suspect there is a problem with the rowid?? > > In article <390F01AD.30A7E5DD@bellsouth.net>, > "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: > > strangiato@my-deja.com wrote: > > > > > > Informix 7.x > > > > > > select p_name,rowid,max(finish_dt) from p_status where id=10 group> by > > > 1,2 > > > > > > gives rise to a syntax error > > > > > > why? > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > Is p_name a column name or a program variable ?? > > > > -- > > John Carlson > > Informix DBA > > WHSmith USA > > > > #include std_disclaimer.h /* These are my opinions, not my > company's > > opinion */ > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. Got it . . . . According to the manual, you can't use ROWID in the select list of a query that contains an aggregate function. I believe that MAX() qualifies. Sorry . . . . -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

strangiato@my-deja.com wrote: > Informix 7.x > > select p_name,rowid,max(finish_dt) from p_status where id=10 group by > 1,2> > gives rise to a syntax error > > why? > > Sent via Deja.com http://www.deja.com/ > Before you buy. Won't comment on the syntax error, but what would the purpose of such an SQL be (given that rowid, in your context, is probably unique)? You might as well structure your SQL as select p_name,rowid,finish_dt from p_status where id=10; to the same conceptual result. Rudy

Thanks! I appreciate your help :) In article <390F1962.82A7A54A@bellsouth.net>, "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: > strangiato@my-deja.com wrote: > > > > It's a column name. I suspect there is a problem with the rowid?? > > > > In article <390F01AD.30A7E5DD@bellsouth.net>, > > "Carlson@WHSmith" <carlson1@bellsouth.net> wrote: > > > strangiato@my-deja.com wrote: > > > > > > > > Informix 7.x > > > > > > > > select p_name,rowid,max(finish_dt) from p_status where id=10 group> > by > > > > 1,2 > > > > > > > > gives rise to a syntax error > > > > > > > > why? > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > Before you buy. > > > > > > Is p_name a column name or a program variable ?? > > > > > > -- > > > John Carlson > > > Informix DBA > > > WHSmith USA > > > > > > #include std_disclaimer.h /* These are my opinions, not my > > company's > > > opinion */ > > > > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > Got it . . . . > > According to the manual, you can't use ROWID in the select list of a > query that contains an aggregate function. I believe that MAX() > qualifies. > > Sorry . . . . > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */ > Sent via Deja.com http://www.deja.com/ Before you buy.