Re: DISTINCT ordering

On 10/8/04 4:56 pm, "Ron St-Pierre" <rstpierre(at)syscor(dot)com> 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);

Because I don¹t want the name in lower case, what I want is

The
the
Z

not:

The
Z
the

>
> otherwise something like:
> SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS ORDER BY
> lower(name);

This is what I have at present, although slightly adjusted, but I wanted to
know if it was possible without 2 selects,

SELECT SS.name FROM (SELECT DISTINCT name FROM someview) SS ORDER BY
lower(name);

Jake

> would return 'foo' twice in the output.
>
>
> Ron
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>