Re: DISTINCT ordering - Mailing list pgsql-novice

From Andrew Hammond
Subject Re: DISTINCT ordering
Date
Msg-id 411927A1.4030808@ca.afilias.info
Whole thread Raw
In response to Re: DISTINCT ordering  (Ron St-Pierre <rstpierre@syscor.com>)
Responses Re: DISTINCT ordering
List pgsql-novice
Ron St-Pierre wrote:
> Jake Stride wrote:
>
>> I have a view from which I select values, but I need to do a 'SELECT
>> DISTINCT' query on a 'varchar' column and order by lower case eg:
>>
>> SELECT DISTINCT name FROM someview ORDER BY lower(name)
>>
> If this is what you want, wouldn't 'Foo' and 'foo' both show up in your
> output? If you only wanted one 'foo' you could use:
>
> SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);
>
> otherwise something like:
> SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS
> ORDER BY lower(name);
> would return 'foo' twice in the output.

Or even

SELECT DISTINCT ON (lower(name)) name
FROM someview
ORDER BY lower(name);

--
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A

Attachment

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: lock entire database
Next
From: David.Ventimiglia@wellsfargo.com
Date:
Subject: PGADMIN III Tools disabled w/ PostgreSQL 8.0 pginstaller