How to sort strings containing a dot?

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

Pierre LEBRECH <pierre.lebrech@laposte.net> writes:

I want to sort strings containing a dot but by taking care of this dot
like any other character.

I suspect what you really want is C locale, and what you've got is
some other locale that uses "dictionary" sort rules. Try "show lc_collate".

regards, tom lane

Thanks Jon, but it does not give me what I want.

But, this gave me an idea : I replaced the select statement by this one :

select name from t order by replace(name, '.', 'z');

And this time it works. With 'z', I get 'co' before 'com'. If I set an 'a', then I get the 'com' before 'co'. Cool!

examples :

dns=> select name from t order by replace(name, '.', 'z');
name
----------
com
com.enta
co.aaa
co.abb
co.cab
co.ment
(6 lines)

dns=> select name from t order by replace(name, '.', 'a');
name
----------
co.aaa
co.abb
co.cab
co.ment
com
com.enta
(6 lines)

Thank you.

Roberts, Jon wrote :

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 :

By the way, I have just inserted a duplicate. Then I have run the
select statement with distinct and I got an error.

How one can solve this?

Does this work?

select distinct name
from ( select name from t order by replace(name, '.', 'a')) as t2

Thanks Adam. No, It doesn't.

But I have found a solution which works :

select name from (select distinct name from t) as name order by replace(name, '.', 'a');
name
----------
co.aaa
co.abb
co.cab
co.ment
com
com.enta
(6 lines)

Thanks to all of you.

Adam Rich wrote :

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

Pierre LEBRECH wrote:

But I have found a solution which works :

select name from (select distinct name from t) as name order by replace(name, '.', 'a');

Shouldn't you replace '.' with a character whose collating sequence is strictly less than
'a'? This solution looks to me as though it might not get ,e.g., 'coast' and 'co.st' in
the desired order except by chance.

--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.