--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:

>
> On Wed, Jun 27, 2012 at 12:22 PM, roydamman <damman@...> wrote:
>
> >
> > 2 records:
> > insert into "TEST" ("TESTID", "NAME") values(1, 'EEN')
> > insert into "TEST" ("TESTID", "NAME") values(2, 'twee')
> >
> > First query:
> > "select "TESTID", "NAME" from test order by upper(2)"
> > Answer (As expected):
> > 1, EEN
> > 2, twee
> >
> > Second query:
> > "select "TESTID", "NAME" from test
> > union select "TESTID", "NAME" from test order by 2"
> > Answer (Unexpected):
> > 1, EEN
> > 2, twee
> > The answer has only two rows. I would expect each row double, in total 4
> > rows. Is this normal behaviour?
> >
>
>
> Yes. By default a UNION includes a projection which eliminates duplicates.
> To get what you want, try
> UNION ALL.
>
>
> >
> > Third query:
> > "select "TESTID", "NAME" from test
> > union select "TESTID", "NAME" from test order by upper(2)"
> > Answer (Unexpected):
> > An error message: ISC ERROR CODE:335544569
> > ISC ERROR MESSAGE:
> > Dynamic SQL Error
> > SQL error code = -104
> > Invalid command
> > invalid ORDER BY clause
> >
> > Especially the third query is annoying me. Is this according to the
> > standard or is it a bug? Why is does "order by upper(2)" not work with an
> > union and work without an union?
>
>
>
> Because in earlier versions of the SQL standard, a query expression (which
> is the technical name for the select in a union) cannot include an order by
> clause. The 2008 standard does allows order by clauses in query
> expressions.
>

Maybe I am wrong but I always thought the "order by" is applied on the results of the two selects in both the second and third union query. And the order by clause does work in the second query (also an union query) "order by 2" and does not work in the third query "order by upper(2)". The only difference is the use of "upper()" which does work with the first query with only one select.

Thanks,

Roy