From: | "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br> |
---|---|
To: | "Jake Stride" <nsuk(at)users(dot)sourceforge(dot)net>, "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: DISTINCT ordering |
Date: | 2004-08-27 00:05:03 |
Message-ID: | 00ff01c48bc9$81954fb0$1600a8c0@NOTELUIZ |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)users(dot)sourceforge(dot)net>
To: "Ron St-Pierre" <rstpierre(at)syscor(dot)com>; "pgsql-novice"
<pgsql-novice(at)postgresql(dot)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(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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-08-27 03:09:12 | Re: pgsql functions and transactions? |
Previous Message | Betsy Barker | 2004-08-26 18:18:57 | pgsql functions and transactions? |