Re: DISTINCT ordering

Andrew Hammond wrote:

> 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);
>
But then only one 'foo' would show up in the results:

Foo
Z

and not:

Foo
foo
Z

which is what he said he wanted.

Ron