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(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of Pierre LEBRECH
>> Sent: Tuesday, March 25, 2008 9:56 AM
>> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>