Re: DISTINCT ordering

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(at)ca(dot)afilias(dot)info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A