Thread: ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)
Hello,
I have following problem:
I have table MY_TABLE with following records:
NAME
-----------
ccc
CCC
AAA
aaa
bbb
BBB
When I use default select that sort all data by NAME:
SELECT * FROM MY_TABLE ORDER BY NAME;
result is following:
NAME
-----------
AAA
BBB
CCC
aaa
bbb
ccc
But I would like to sort all data as following:
NAME
-----------
AAA
aaa
BBB
bbb
CCC
ccc
How can I write sql command (or set up ORDER BY options) for selecting that?
Thank you in advance for answer,
best regards,
Julian Legeny
mailto:julo@opensubsystems.org
Re: ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)
SELECT * FROM MY_TABLE ORDER BY LOWER(NAME);
Thanks
Dinesh Pandey
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Julian Legeny
Sent: Tuesday, May 10, 2005 3:12 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)
Hello,
I have following problem:
I have table MY_TABLE with following records:
NAME
-----------
ccc
CCC
AAA
aaa
bbb
BBB
When I use default select that sort all data by NAME:
SELECT * FROM MY_TABLE ORDER BY NAME;
result is following:
NAME
-----------
AAA
BBB
CCC
aaa
bbb
ccc
But I would like to sort all data as following:
NAME
-----------
AAA
aaa
BBB
bbb
CCC
ccc
How can I write sql command (or set up ORDER BY options) for selecting that?
Thank you in advance for answer,
best regards,
Julian Legeny
mailto:julo@opensubsystems.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Re: ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)
On Tue, 10 May 2005 07:41 pm, Julian Legeny wrote: > Hello, > > I have following problem: > But I would like to sort all data as following: > > NAME > ----------- > AAA > aaa > BBB > bbb > CCC > ccc > > > How can I write sql command (or set up ORDER BY options) for selecting that? > SELECT * FROM MY_TABLE ORDER BY lower(NAME), NAME The second NAME is to ensure that AAA comes before aaa, otherwise the order is undetermined.
if you order by upper(name) then it will mix them all together, so you won't have capital before lowercase, but it will put all the lowercase a before the uppercase b "Julian Legeny" <julo@opensubsystems.org> wrote in message news:559596967.20050510114150@opensubsystems.org... > Hello, > > I have following problem: > > I have table MY_TABLE with following records: > > NAME > ----------- > ccc > CCC > AAA > aaa > bbb > BBB > > When I use default select that sort all data by NAME: > > SELECT * FROM MY_TABLE ORDER BY NAME; > > result is following: > > NAME > ----------- > AAA > BBB > CCC > aaa > bbb > ccc > > > But I would like to sort all data as following: > > NAME > ----------- > AAA > aaa > BBB > bbb > CCC > ccc > > > How can I write sql command (or set up ORDER BY options) for selecting that? > > > Thank you in advance for answer, > best regards, > > Julian Legeny > > mailto:julo@opensubsystems.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Tue, 2005-05-10 at 11:41 +0200, Julian Legeny wrote: > ... > But I would like to sort all data as following: > > NAME > ----------- > AAA > aaa > BBB > bbb > CCC > ccc > How can I write sql command (or set up ORDER BY options) for selecting that? how about ORDER BY lower(NAME),NAME ? gnari
Re: ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)
Hello, that's what I was looking for. Thanks to all for advices, with best regards, Julian Legeny Tuesday, May 10, 2005, 12:14:38 PM, you wrote: RS> SELECT * FROM MY_TABLE ORDER BY lower(NAME), NAME RS> The second NAME is to ensure that AAA comes before aaa, otherwise the order is undetermined. RS> ---------------------------(end of broadcast)--------------------------- RS> TIP 2: you can get off all lists at once with the unregister command RS> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)