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@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
>