Re: DISTINCT ordering

On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre(at)syscor(dot)com> wrote:

> 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

I must have misunderstood what you meant, sorry. Andrew Hammonds answer
works how I want it to, I guess my example was a little trival, my solution
was needed to over come the following ordering:

The company
The one more company
the another company

So that is was

the another company
The company
The one more company

(in a contacts database)

Thanks

Jake

>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>