Re: order by question

[Steve Clark <sclark@xxxxxxxxxxxxx>]

On 08/07/2014 11:36 AM, Kevin Grittner wrote:
> Steve Clark <sclark@xxxxxxxxxxxxx> wrote:
>
> > I am confused by how postgres 8,4..13 is sorting my data.
> > select * from test order by data;
> >       data
> > ----------
> >
> >     -
> >     --
> >     1
> >     11
> >     11F
> >     1F
> >     a
> >     b
> >     C
> >     F
> >     -F
> >     Feneric
> >     Generic
> > (14 rows)
> >
> > The first row is a single space, the next row a single -, the next two -- .
> > What I don't understand is why the '-F', the ' Feneric' and
> > the ' Generic' sort where they do.
> >
> > I would expect the output to be like this:
> >
> >       data
> > ----------
> >
> >     Feneric
> >     Generic
> >     -
> >     --
> >     -F
> >     1
> >     11
> >     11F
> >     1F
> >     a
> >     b
> >     C
> >     F
> > (14 rows)
> >     lc_collate
> > -------------
> >     en_US.UTF-8
> PostgreSQL uses the OS collations.  What you are getting matches my
> Ubuntu 14.04 machine:
>
> kgrittn@Kevin-Desktop:~/pg/master$ echo $LANG
> en_US.UTF-8
> kgrittn@Kevin-Desktop:~/pg/master$ sort <<XXX
> >     Feneric
> >     Generic
> >     -
> >     --
> >     -F
> >     1
> >     11
> >     11F
> >     1F
> >     a
> >     b
> >     C
> >     F
> > XXX
>    -
>    --
>    1
>    11
>    11F
>    1F
>    a
>    b
>    C
>    F
>    -F
>    Feneric
>    Generic
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
Hi Kevin,

Thanks for the response. I get the same thing as postgres if I sort from the command line too. But I don't understand why.

I would expect '-F' to sort immediately after '-' and '--' not after 'F' as
well as ' Feneric' (spaceFeneric) and ' Generic' (spaceGeneric) to sort immediately after ' ' (space).

It is like the space character and the - in -Letter is ignored.


--
Stephen Clark



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general