Thread: 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')

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

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
>>
>

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