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

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

> >> AFAIU the SQL standard doesn't allow it at all to order by fields which
> > aren't in the select-list, but that's too
> >> restrictive IMO:
> >
> > Indeed, it doesn't, and for good reason, IMO.
> >
> > Each resultset is a derived table and you can order by columns in the
> > resultset only.
> >
> > What sense does it make to order by a set of values not in your
resultset?
> > The
> > resultset itself could appear to be random (no ordering).
> >
>
> In a real world SQL output is often not viewed directly, but passed to
> client software, that has a feature of lookup fields. Suppose:
>
> select t1.value, t1.t2link
> from t1 join t2 on t1.t2link = t2.id
> order by t2.value
>
> At the client side t1.t2link is not displayed directly. Instead
> clientside lookup is done, and user sees t1.value and t2.value in
> dataset, ordered by t2.value - which is exactly what he wants. Therefore
> I think that standard is too restrictive and we should not follow it in
> such a case. Certainly, we may say - dear user, please add t2.value to
> your query result set. But suppose that t1 contains 10000 rows, t2 - 10
> rows, average size of t2.value is 100 byte - in that case adding of
> t2.value to a query leads to additional megabyte of traffic. And if slow
> WAN connection is used, this is not good. Client lookups efficiently
> save traffic in such cases, and order by column not in result set is
> required support for it.

If you have a client-side resultset of 10,000 rows, you're in for
some trouble anyway :-)

If you always gonna fetch t2.value, why don't include it up front?

And no, I'm not talking about "fetch on demand" detail lookups.

No offence Alex, but your example is far fetched.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com