| Lists: | pgsql-sql |
|---|
| From: | "Tomasz Myrta" <jasiek(at)klaster(dot)net> |
|---|---|
| To: | Jodi Kanter <jkanter(at)virginia(dot)edu>, Postgres SQL List <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: order by |
| Date: | 2003-03-26 11:29:14 |
| Message-ID: | 20030326192914.M43302@klaster.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
> Is there a known problem with the SQL order by clause within
> postgres version 7.2.3? I am trying to sort a field which holds file
> names that have some spaces as well non-alphanumeric characters,
> including underscores (see below).
>
> The results of:
> select study_name from study order by study_name;
>
> Study1a
> Study 1b
> Study 1c
> Study1d
>
> The results of sorting in Perl (which uses Linux's underlying sort
> function)
>
> Study 1c
> Study 1b
> Study1a
> Study1d
>
> Is there any way around this difference? Blank spaces should show up
> first in the sort.
I made a test table with varchar field and filled it with your values.
I got the same result as in Perl. I have Postgresql 7.3. I'm not sure what
you really want to get.
template1=# create table test (x varchar);
template1=# insert into test values ('Study1a');
template1=# insert into test values ('Study 1b');
template1=# insert into test values ('Study 1c');
template1=# insert into test values ('Study1d');
template1=# SELECT * from test order by x asc;
x
-----------
Study 1c
Study 1b
Study1a
Study1d
(4 rows)
Regards,
Tomasz Myrta
| From: | Jodi Kanter <jkanter(at)virginia(dot)edu> |
|---|---|
| To: | Postgres SQL List <pgsql-sql(at)postgresql(dot)org> |
| Subject: | order by |
| Date: | 2003-03-26 18:12:30 |
| Message-ID: | 005501c2f3c3$43f8db30$de138f80@virginia.edu |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
Is there a known problem with the SQL order by clause within postgres version 7.2.3?
I am trying to sort a field which holds file names that have some spaces as well non-alphanumeric characters, including underscores (see below).
The results of:
select study_name from study order by study_name;
Study1a
Study 1b
Study 1c
Study1d
The results of sorting in Perl (which uses Linux's underlying sort function)
Study 1c
Study 1b
Study1a
Study1d
Is there any way around this difference? Blank spaces should show up first in the sort.
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter(at)virginia(dot)edu
| From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
|---|---|
| To: | Jodi Kanter <jkanter(at)virginia(dot)edu> |
| Cc: | Postgres SQL List <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: order by |
| Date: | 2003-03-26 18:44:33 |
| Message-ID: | 20030326104353.P63544-100000@megazone23.bigpanda.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
On Wed, 26 Mar 2003, Jodi Kanter wrote:
> Is there a known problem with the SQL order by clause within postgres
> version 7.2.3? I am trying to sort a field which holds file names that
> have some spaces as well non-alphanumeric characters, including
> underscores (see below).
>
> The results of:
> select study_name from study order by study_name;
>
>
> Study1a
> Study 1b
> Study 1c
> Study1d
>
>
> The results of sorting in Perl (which uses Linux's underlying sort function)
>
> Study 1c
> Study 1b
> Study1a
> Study1d
>
> Is there any way around this difference? Blank spaces should show up
> first in the sort.
Are you in "C" locale or something like "en_US"? In the latter, the first
is the "correct" ordering.