--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 05:32 AM 12/04/2005 +0000, you wrote:
> > > > SELECT T1.*,T2.A WHERE T2.ID=T1.ID
> > > > UNION
> > > > SELECT T1.*,T1.B WHERE T1.ID=-1
> > > >
> > > > ORDER BY 0 /or T1.C
> > > >
> > wrong?
> > >
> > > ORDER BY <column ordinal, starting at 1>
> > >
>
> At 05:32 AM 12/04/2005 +0000, Graeme Edwards wrote:
>
>
> >In the example above using the union clause, I don't believe that
> >you can order by an ordinal field number when using the t1.*

syntax.

> >You may have to explicitly name the fields in order to do this.
> >Correct me if I am wrong.
>
> Why not? Did you test it? It's only saying "order by the first

field in

> the spec, regardless of what it is"...
>
> The syntax would come to grief sooner than that point, if the two
> corresponding output fields weren't type-compatible for forming

the

> union....but that's not the case here.
>
> <pedant hat on>
> Of course, whether using select * at all is good practice is

another story.

> </>


> ./hb

I apologise for making the statement without testing it, but I was
sure that I couldn't get it to work for me in the past.
However I did realise after actually re-testing what I had done that
I had tried in the past the syntax

Select *,calculated_field from t1
union
select *,calculated_field from t2
order by 1

This syntax is not accepted even though there can be no ambiguity
about the * means. You get an error message mentioning an invalid
token. You have to specify t1.* before it will work.
The problem was never with the order by ordinal syntax at all.

This is something that I didn't realise at the time I was doing
this, and obviously I should have tested it before commenting here.
I take the point that in most cases Select * is not good practice.