SEO

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

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
>

SELECT * FROM MY_TABLE ORDER BY LOWER(NAME);





Thanks

Dinesh Pandey



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-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

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




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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)



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org