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@xxxxxxxxxxx] > Sent: Tuesday, March 25, 2008 11:42 AM > To: Roberts, Jon > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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@xxxxxxxxxxxxxx [mailto:pgsql-general- > >> owner@xxxxxxxxxxxxxx] On Behalf Of Pierre LEBRECH > >> Sent: Tuesday, March 25, 2008 9:56 AM > >> To: pgsql-general@xxxxxxxxxxxxxx > >> Subject: 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@xxxxxxxxxxxxxx) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
