Subject Re: SELECT UNION with ORDER BY?!
Author graeme_edwards2000
--- In, Helen Borrie <helebor@t...>
> 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.*
> >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
> 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
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.