Re: Question on Bizarre Sorting (ORDER BY in 7.1) (fwd)

On Tue, 24 Apr 2001, Clayton Vernon wrote:
>seems to be treating 'threadid' as numeric, not alpha.
>did you run any macros that needed to guess the format of the field? many of
>these are very poor in terms of looking down the table. sometimes the first
>row can inadvertently define things for you.

Nope, to my knowledge nothing is going on except for normal INSERTs
and SELECTs. What confuses me is that 'threadid' is of type text, and even
if it were being treated as if it were of type numeric, its results are
still inconsistent, and somewhat inscrutable.

I can't see any reasonable way that this sorting:

>>lxp=# SELECT threadid FROM test ORDER BY threadid;
>> threadid
>>----------------------
>> 000-0987877374-00313
>> ___-0987877410-00316
>> ___-0987877430-00317
>> 100-0987877381-00314
>> 100-0987877395-00315
>> 200-0987877461-00318
>>(6 rows)

...would be occuring, unless it's going out of its way to try to strip
non-numeric characters and treat the remaining assembled numbers as a
whole number to sort by, possibly?

But if that were the case, that still doesn't explain why the substrings
(below) behave the way that they do, in re-ordering the underscores.

>lxp=# SELECT substr(threadid,1,5) FROM test ORDER BY substr(threadid, 1, 5);
> substr
>--------
> ___-0
> ___-0
> 000-0
> 100-0
> 100-0
> 200-0
>(6 rows)
>> Now, the underscores appear to PRECEDE the 0's. This seems at least a
>little more sane, however this is completely the opposite of where the
>underscore would be sorted with 7.0.3. Now consider the next substring, of
>six characters instead of five.
>
>lxp=# SELECT substr(threadid,1,6) FROM test ORDER BY substr(threadid, 1,6);
> substr
>---------
> 000-09
> ___-09
> ___-09
> 100-09
> 100-09
> 200-09
>(6 rows)

Thanks, though still puzzled,
Jw @ Command Prompt.
--
By way of pgsql-general(at)commandprompt(dot)com