Re: [Firebird-devel] Using DISTINCT and ORDER BYtogether andunpredictable results

[Martijn T. <m.t...@up...>]

> > Suppose you have a table:
> >
> > X   Y
> > --- ---
> >    1   3
> >    1   5
> >    2   4
> >
> > What should the following query return: SELECT DISTINCT X ORDER BY Y?
> >
> > 1
> > 2
> >
> > or
> >
> > 2
> > 1
>
> The user asked for a hidden field to drive the order, then I don't care.
>
> I can be convinced that people are afraid of DISTINCT v/s SORT on hidden
> field and forbid it, but not the general case.

No offence, but this is exactly why a SORT on an "original table column"
doesn't
make sense.

The _resultset_ should be sorted which means you can only sort on values
in the resultset.

Yes, it might _sometimes_ make sense to sort by a "hidden column" from the
original table, but in general, I would say it's rubbish.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com