Re: DISTINCT ordering

I don't know if is so late, but you can use something like

SELECT DISTINCT name , lower(name) AS lower_name FROM someview ORDER BY 2

Luiz

----- Original Message -----
From: "Jake Stride" <nsuk@users.sourceforge.net>
To: "Ron St-Pierre" <rstpierre@syscor.com>; "pgsql-novice"
<pgsql-novice@postgresql.org>
Sent: Wednesday, August 11, 2004 4:06 AM
Subject: Re: [NOVICE] DISTINCT ordering


> 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