From: | Justin Clift <justin(at)postgresql(dot)org> |
---|---|
To: | pgsql-general(at)commandprompt(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question on Bizarre Sorting (ORDER BY in 7.1) (fwd) |
Date: | 2001-04-25 11:08:07 |
Message-ID: | 3AE6B017.F75F8E42@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dunno if this is helpful, but there is a pattern.
After stripping off the leading non-numeric characters, from the results
in the previous email, this leaves :
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)
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)
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)
As for why? Don't know.
But the sorting is consistent.
Regards and best wishes,
Justin Clift
pgsql-general(at)commandprompt(dot)com wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Handler | 2001-04-25 11:09:39 | Problem importing auto-increment fields in MS Access to PostgreSQL |
Previous Message | Jason | 2001-04-25 11:03:37 | help with serial type |