Subject Re: Finding Unique Records with Union
Author Adam
--- In, "legrand_legrand_63"
<grand.brun.63@...> wrote:
> oups ...
> you are perfectly right
> For derived tables, I have spécified in the initial message that it is
> a FB 2.0 New Feature.
> At last, Oracle doesn't support syntax:
> select distinct A from
> (select A,B from T)
> order by B
> and returns error ORA-01791 ... when FB doesn't beep.
> Is that standard compliant ?

I am not sure what FB 2 does because I am running FB 1.5.3.

Lets simplify the query a bit more:

select A
from SomeTable
order by B

IIRC, Oracle does not accept that syntax, but Firebird does. I do not
know what the SQL standard says of such queries, but logically there
is no ambiguity so I do not have a problem with it.

Lets make it slightly more complex:

select distinct A
from SomeTable
order by B

Here it gets messy, because you may have two records with identical A
values but different B values. You point out that Oracle rejects that
query, and it is correct to do so.

I have just tested, and Firebird accepts the query. I have not had a
chance to understand its behaviour, but such a query does not make any
sense to me. Again, I do not know what the SQL standard says about
this particular query, but I do not imagine it would be a legitimate