select name from t group by name order by replace(name, '.', 'a');
I personally don't use distinct very often as group by is usually more
flexible and quicker to add aggregates to the query.
Jon
> -----Original Message-----
> From: Pierre LEBRECH [mailto:pierre.lebrech@laposte.net]
> Sent: Tuesday, March 25, 2008 11:42 AM
> To: Roberts, Jon
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to sort strings containing a dot?
>
> By the way, I have just inserted a duplicate. Then I have run the
select
> statement with distinct and I got an error.
>
>
> select distinct name from t order by replace(name, '.', 'a');
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in
select
> list
>
> How one can solve this?
>
>
> Note : If I remove the distinct word, It works and I get this :
>
> select name from t order by replace(name, '.', 'a');
> name
> ----------
> co.aaa
> co.abb
> co.cab
> co.ment
> co.ment
> com
> com.enta
> (7 lines)
>
>
> Roberts, Jon wrote :
> > create table t (name varchar);
> >
> > insert into t values ('co.aaa');
> > insert into t values ('co.abb');
> > insert into t values ('co.cab');
> > insert into t values ('com');
> > insert into t values ('co.ment');
> > insert into t values ('com.enta');
> >
> > select name from t order by replace(name, '.', '');
> >
> >
> > Jon
> >
> >> -----Original Message-----
> >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> >> owner@postgresql.org] On Behalf Of Pierre LEBRECH
> >> Sent: Tuesday, March 25, 2008 9:56 AM
> >> To: pgsql-general@postgresql.org
> >> Subject: [GENERAL] How to sort strings containing a dot?
> >>
> >> Hello,
> >>
> >> I want to sort strings containing a dot but by taking care of this
dot
> >> like any other character.
> >>
> >> example :
> >>
> >> Currently, I get this after the sort :
> >>
> >> co.aaa
> >> co.abb
> >> co.cab
> >> com
> >> co.ment
> >> com.enta
> >>
> >> But I would like to get this :
> >>
> >> co.aaa
> >> co.abb
> >> co.cab
> >> co.ment
> >> com
> >> com.enta
> >>
> >> How I can do this?
> >> Thank you
> >>
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
