Thread: DISTINCT ordering
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) Obviously this doesn't work with the 'DISTINCT' but is there any way to do it apart from: SELECT v.name FROM (SELECT DISTINCT name FROM someview) v ORDER BY lower(v.name) Or is this the most efficient way? Thanks Jake
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. Ron
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); -- Andrew Hammond 416-673-4138 ahammond@ca.afilias.info Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
Attachment
On 10/8/04 4:56 pm, "Ron St-Pierre" <rstpierre@syscor.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 >
Jake Stride wrote: >Because I don¹t want the name in lower case, what I want is > >The >the >Z > >not: > >The >Z >the > > > >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 > > In that case I don't think that you can do it without a subquery. I think that if the db was configured with a different locale (en_GB or en_US) it would sort the data as you want it, but I think that other problems might occur. I haven't dealt with the locale settings much myself, but there are frequently questions(problems?) such as yours which show up on the GENERAL discussion list. You can search the GENERAL archives and documentation if you want more background on this it it's a big problem. Or perhaps someone with more 'locale' knowledge might confirm this. hth Ron
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
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 >
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 >
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