On Wed, Jun 27, 2012 at 12:22 PM, roydamman <[email protected]> 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. Good luck, Ann [Non-text portions of this message have been removed]
