Subject Re: [ib-support] ORDER BY on BLOB
Author Claudio Valderrama C.
""Kenneth Foo"" <kenneth@...> wrote in message
> Hi
> Is sorting by contents of text blobs possible on IB/FB?
> I tried something like this
> select * from messages order by msg_from;
> where msg_from is a blob field subtype 1. (Text).
> What I got was totally off.
> Is IB/FB sorting against BLOB ID instead?

Yes, although maybe it simply should complain.
FB may do even odder sort due to code I was doing, but the documentation
states you can't (you shouldn't).

> If yes, how then do I sort by BLOB contents?

Due to the variable size of blobs, it's not possible nor desirable. It would
be a huge performance drain and jack of kludges, since a simple blob can
span 2GB. The internal temporary sort file would be gruesome. The same
reason blobs aren't indexed. If your blobs are reasonable short, try this:

select substring(msg_from from 1 for 1000)
from messages
order by 1;

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing