Hi Tim,

...
group by 1

isn't enough?

Ali

--- In firebird-support@yahoogroups.com, Tim Gahnström
<tim.gahnstrom@i...> wrote:

> > -----Original Message-----
> > From: Martijn Tonies [mailto:m.tonies@u...]
> > > begin
> > > for
> > > select substring(name from 1 for 1) as letter
> > > from T_MOVIES
> > > group by letter
> > > into :ltr_out
> > > do suspend;
> > > end
> > >
> > > I get the error message
> > > --------
> > > Column unknown.
> > > LETTER.
> > > --------
> > >
> > > I have also tried omitting the AS keyword but I guess there

are some

> > other
> > way to name the output from substring function.
> >
> > Indeed.
> >
> > Try a:
> > group by substring(name from 1 for 1)
>
> Thanks a lot! It solved the problem in a heartbeat.
>
> I would still have preferred a named column because of the cleaner

look and probably speed but if there aren't one this will do

>
> I don't know how it is implemented but it certainly looks like the

below will make two runs through the table, once in the select and
once in the group by doing essentially the same thing.

>
> select substring(name from 1 for 1)
> from T_MOVIES
> where name=name
> group by substring(name from 1 for 1)
> into :ltr_out
>
> Many thanks
>
> Tim