Обсуждение: postgresql order lowercase before uppercase
Hello, I need to as follow: ande Amma Anit Anti Brac Cali .... I have try ORDER by name COLLATE 'C'; but this order as follow: Amma Anit Anti Brac Cali .... ande Best Regards
Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause? Bobb > -----Original Message----- > From: basti <mailinglist@unix-solution.de> > Sent: Thursday, March 18, 2021 9:40 AM > To: pgsql-general@postgresql.org > Subject: postgresql order lowercase before uppercase > > Think Before You Click: This email originated outside our organization. > > > Hello, > I need to as follow: > > ande > Amma > Anit > Anti > Brac > Cali > .... > > I have try ORDER by name COLLATE 'C'; > > but this order as follow: > > Amma > Anit > Anti > Brac > Cali > .... > ande > > Best Regards >
This does not help in that case. On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote: > Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause? > > Bobb > > > >> -----Original Message----- >> From: basti <mailinglist@unix-solution.de> >> Sent: Thursday, March 18, 2021 9:40 AM >> To: pgsql-general@postgresql.org >> Subject: postgresql order lowercase before uppercase >> >> Think Before You Click: This email originated outside our organization. >> >> >> Hello, >> I need to as follow: >> >> ande >> Amma >> Anit >> Anti >> Brac >> Cali >> .... >> >> I have try ORDER by name COLLATE 'C'; >> >> but this order as follow: >> >> Amma >> Anit >> Anti >> Brac >> Cali >> .... >> ande >> >> Best Regards >> >
to me, collate 'C' ask for the raw ascii order which put caps before because the hexa coding is lower
did you try any thing else (POSIX is same as 'C')
On Thu, Mar 18, 2021 at 4:18 PM basti <mailinglist@unix-solution.de> wrote:
This does not help in that case.
On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:
> Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause?
>
> Bobb
>
>
>
>> -----Original Message-----
>> From: basti <mailinglist@unix-solution.de>
>> Sent: Thursday, March 18, 2021 9:40 AM
>> To: pgsql-general@postgresql.org
>> Subject: postgresql order lowercase before uppercase
>>
>> Think Before You Click: This email originated outside our organization.
>>
>>
>> Hello,
>> I need to as follow:
>>
>> ande
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>>
>> I have try ORDER by name COLLATE 'C';
>>
>> but this order as follow:
>>
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>> ande
>>
>> Best Regards
>>
>
Exactly. "C" collation is the opposite of what he wants.
OP needs something like "de_DE".
OP needs something like "de_DE".
On 3/18/21 10:36 AM, Marc Millas wrote:
to me, collate 'C' ask for the raw ascii order which put caps before because the hexa coding is lowerdid you try any thing else (POSIX is same as 'C')On Thu, Mar 18, 2021 at 4:18 PM basti <mailinglist@unix-solution.de> wrote:This does not help in that case.
On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:
> Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause?
>
> Bobb
>
>
>
>> -----Original Message-----
>> From: basti <mailinglist@unix-solution.de>
>> Sent: Thursday, March 18, 2021 9:40 AM
>> To: pgsql-general@postgresql.org
>> Subject: postgresql order lowercase before uppercase
>>
>> Think Before You Click: This email originated outside our organization.
>>
>>
>> Hello,
>> I need to as follow:
>>
>> ande
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>>
>> I have try ORDER by name COLLATE 'C';
>>
>> but this order as follow:
>>
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>> ande
>>
>> Best Regards
>>
>
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
or, maybe, just nothing ?
On Thu, Mar 18, 2021 at 4:44 PM Ron <ronljohnsonjr@gmail.com> wrote:
Exactly. "C" collation is the opposite of what he wants.
OP needs something like "de_DE".On 3/18/21 10:36 AM, Marc Millas wrote:to me, collate 'C' ask for the raw ascii order which put caps before because the hexa coding is lowerdid you try any thing else (POSIX is same as 'C')On Thu, Mar 18, 2021 at 4:18 PM basti <mailinglist@unix-solution.de> wrote:This does not help in that case.
On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:
> Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause?
>
> Bobb
>
>
>
>> -----Original Message-----
>> From: basti <mailinglist@unix-solution.de>
>> Sent: Thursday, March 18, 2021 9:40 AM
>> To: pgsql-general@postgresql.org
>> Subject: postgresql order lowercase before uppercase
>>
>> Think Before You Click: This email originated outside our organization.
>>
>>
>> Hello,
>> I need to as follow:
>>
>> ande
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>>
>> I have try ORDER by name COLLATE 'C';
>>
>> but this order as follow:
>>
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>> ande
>>
>> Best Regards
>>
>--
Angular momentum makes the world go 'round.
On Thu, 2021-03-18 at 15:39 +0100, basti wrote: > I need to as follow: > > ande > Amma > Anit > Anti > Brac > Cali > .... > > I have try ORDER by name COLLATE 'C'; > > but this order as follow: > > Amma > Anit > Anti > Brac > Cali > .... > ande Create an ICU collation: CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US@CaseFirst=LowerFirst"); Then use ORDER BY name COLLATE inv Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Am 18.03.21 um 17:19 schrieb Laurenz Albe: > On Thu, 2021-03-18 at 15:39 +0100, basti wrote: >> I need to as follow: >> >> ande >> Amma >> Anit >> Anti >> Brac >> Cali >> .... >> >> I have try ORDER by name COLLATE 'C'; >> >> but this order as follow: >> >> Amma >> Anit >> Anti >> Brac >> Cali >> .... >> ande > > Create an ICU collation: > > CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US@CaseFirst=LowerFirst"); > > Then use > > ORDER BY name COLLATE inv > > Yours, > Laurenz Albe > I have try the inv collate, that does not work on PG 9.6 and 10. Order is the same as without collate. SELECT a.name FROM foo as a LEFT JOIN (SELECT name from foo WHERE name = 'lowercase name') as b on a.name = b.name ORDER by b.name,a.name does it. perhaps there is a better way? There is only one name in lowercase and this one must be the first one.
On Thu, 2021-03-18 at 23:51 +0100, basti wrote: > Am 18.03.21 um 17:19 schrieb Laurenz Albe: > > On Thu, 2021-03-18 at 15:39 +0100, basti wrote: > > > I need to as follow: > > > > > > ande > > > Amma > > > Anit > > > Anti > > > Brac > > > Cali > > > .... > > > > Create an ICU collation: > > > > CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US@CaseFirst=LowerFirst"); > > I have try the inv collate, that does not work on PG 9.6 and 10. > > Order is the same as without collate. > > SELECT a.name > FROM foo as a > LEFT JOIN (SELECT name from foo WHERE name = 'lowercase name') as b > on a.name = b.name > ORDER by b.name,a.name > > does it. > > perhaps there is a better way? Ah, I misunderstood what you want. SELECT a.name FROM foo as a ORDER BY a.name <> lower(a.name), a.name; That works because FALSE < TRUE. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 19.03.21 08:33, Laurenz Albe wrote: > On Thu, 2021-03-18 at 23:51 +0100, basti wrote: >> Am 18.03.21 um 17:19 schrieb Laurenz Albe: >>> On Thu, 2021-03-18 at 15:39 +0100, basti wrote: >>>> I need to as follow: >>>> >>>> ande >>>> Amma >>>> Anit >>>> Anti >>>> Brac >>>> Cali >>>> .... >>> >>> Create an ICU collation: >>> >>> CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US@CaseFirst=LowerFirst"); >> >> I have try the inv collate, that does not work on PG 9.6 and 10. >> >> Order is the same as without collate. >> >> SELECT a.name >> FROM foo as a >> LEFT JOIN (SELECT name from foo WHERE name = 'lowercase name') as b >> on a.name = b.name >> ORDER by b.name,a.name >> >> does it. >> >> perhaps there is a better way? > > Ah, I misunderstood what you want. > > SELECT a.name > FROM foo as a > ORDER BY a.name <> lower(a.name), a.name; > > That works because FALSE < TRUE. > > Yours, > Laurenz Albe > This does not work for me. The same result as normal order. Amm andere Marken Ani Anti Bra Cali Test data attached.
Вложения
On 19.03.21 10:12, basti wrote: > On 19.03.21 08:33, Laurenz Albe wrote: >> On Thu, 2021-03-18 at 23:51 +0100, basti wrote: >>> Am 18.03.21 um 17:19 schrieb Laurenz Albe: >>>> On Thu, 2021-03-18 at 15:39 +0100, basti wrote: >>>>> I need to as follow: >>>>> >>>>> ande >>>>> Amma >>>>> Anit >>>>> Anti >>>>> Brac >>>>> Cali >>>>> .... >>>> Laurenz' approach is sound, it just needs a little tweak to not trip up on the "andere Marken" uppercase M. Try this: select id, marke.name from marke ORDER BY left(marke.name,1) <> left(lower(marke.name),1), marke.name Fiddle: http://sqlfiddle.com/#!17/d9d83e/9 Cheers Christian -- Christian Ramseyer, netnea ag Network Management. Security. OpenSource. https://www.netnea.com
On Fri, 2021-03-19 at 10:12 +0100, basti wrote: > >> SELECT a.name > >> FROM foo as a > >> LEFT JOIN (SELECT name from foo WHERE name = 'lowercase name') as b > >> on a.name = b.name > >> ORDER by b.name,a.name > >> > >> does it. > >> > >> perhaps there is a better way? > > > > Ah, I misunderstood what you want. > > > > SELECT a.name > > FROM foo as a > > ORDER BY a.name <> lower(a.name), a.name; > > > > That works because FALSE < TRUE. > > This does not work for me. The same result as normal order. > > Amm > andere Marken > Ani > Anti > Bra > Cali You are allowed to adapt the query to your special needs. But if you need to be spoonfed: SELECT a.name FROM foo as a ORDER BY a.name = initcap(a.name), a.name; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com