Re: DISTINCT ordering

Why not just do?

SELECT DISTINCT name, LOWER(name) FROM someview ORDER BY lower(name)








Jake Stride wrote:

> On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre@syscor.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@postgresql.org so that your
>>    message can get through to the mailing list cleanly
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>