order by question.

Your query doesn't appear to include an "order by" clause. The
results won't be sorted without an "order by".

On May 3, 2007, at 4:12 PM, Jose Blanco wrote:

I’m using version 7.3 of postgres and when I issue the following
command

select author, sort_author from itemsbyauthor where sort_author
like 'tan%';

I get the following results:

Author sort_author

Tan, Weihong | tan, weihong

Tang, S. C. | tang, s. c.

Tan, Fang | tan, fang

Note how the tan’s are not grouped together when I think they
should be. Is this something that is fixed in a more current
version of postgres?

Thank you!

Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938

I'll try this question again.

_____

From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Jose Blanco
Sent: Thursday, May 03, 2007 4:12 PM
To: pgsql-bugs@postgresql.org
Subject: [BUGS] order by question.

I'm using version 7.3 of postgres and when I issue the following command

select author, sort_author from itemsbyauthor where sort_author like 'tan%';

I get the following results:

Author sort_author

Tan, Weihong | tan, weihong

Tang, S. C. | tang, s. c.

Tan, Fang | tan, fang

Note how the tan's are not grouped together when I think they should be. Is
this something that is fixed in a more current version of postgres?

Thank you!

"Jose Blanco" <blancoj@umich.edu> writes:

I'll try this question again.

The answer is the same as before: you didn't specify an ORDER BY clause,
therefore there is no reason to expect any particular output ordering.
Whatever ordering you might happen to see is an implementation artifact.

regards, tom lane

This second time I did, see

select author, sort_author from itemsbyauthor where sort_author like 'tan%'
order by 2;

"order by 2"

Or am I not understanding something?

Thanks!

-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, May 04, 2007 2:24 PM
To: Jose Blanco
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] order by question.

"Jose Blanco" <blancoj@umich.edu> writes:

I'll try this question again.

The answer is the same as before: you didn't specify an ORDER BY clause,
therefore there is no reason to expect any particular output ordering.
Whatever ordering you might happen to see is an implementation artifact.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Jose Blanco wrote:

This second time I did, see

select author, sort_author from itemsbyauthor where sort_author like
'tan%' order by 2;

No, your posts didn't contain that query.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Fri, 4 May 2007, Jose Blanco wrote:

This second time I did, see

select author, sort_author from itemsbyauthor where sort_author like 'tan%'
order by 2;

"order by 2"

Or am I not understanding something?

One issue you might not realize is that the sort order for some locales
ignore symbols and spaces for initial scans, so for example, you might
find that "A Z" > "AB" while " " < "B". Specifically, for the data you
gave, en_US would order them "tan, fang", "tang, s. c.", "tan, weihong"
while C would order them "tan, fang", "tan, weihong", "tang, s. c.".

I'm not sure what you mean by "C" and how do I change this?

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Friday, May 04, 2007 3:38 PM
To: Jose Blanco
Cc: 'Tom Lane'; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] order by question.

On Fri, 4 May 2007, Jose Blanco wrote:

This second time I did, see

select author, sort_author from itemsbyauthor where sort_author like

'tan%'

order by 2;

"order by 2"

Or am I not understanding something?

One issue you might not realize is that the sort order for some locales
ignore symbols and spaces for initial scans, so for example, you might
find that "A Z" > "AB" while " " < "B". Specifically, for the data you
gave, en_US would order them "tan, fang", "tang, s. c.", "tan, weihong"
while C would order them "tan, fang", "tan, weihong", "tang, s. c.".

* Jose Blanco <blancoj@umich.edu> [040507, 14:28]:

This second time I did, see

select author, sort_author from itemsbyauthor where sort_author like 'tan%'
order by 2;

"order by 2"

Or am I not understanding something?

Hi Jose,
have you tried to modify your query as others suggested? i.e.:

--> select author, sort_author from itemsbyauthor where sort_author like
'tan% ORDER BY sort_author;
^^^^^^^^^^^^^^^^^^^^
HTH
Regards,
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (�|�)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]

On Fri, 4 May 2007, Jose Blanco wrote:

I'm not sure what you mean by "C" and how do I change this?

It's a locale name. The ordering is effectively byte order ordering,
while many other locales like en_US have more interesting sorting rules.
IIRC, the locale can only be set at initdb time currently, so changing it
requires re-initialize the database directory.

* Ennio-Sr <nasr.laili@tin.it> [040507, 23:52]:

* Jose Blanco <blancoj@umich.edu> [040507, 14:28]:

This second time I did, see

select author, sort_author from itemsbyauthor where sort_author like 'tan%'
order by 2;

Hi Jose,
have you tried to modify your query as others suggested? i.e.:

--> select author, sort_author from itemsbyauthor where sort_author like
'tan%' ORDER BY sort_author;
^^^^^^^^^^^^^^^^^^^^

Oops ... I missed your "order by 2" ... Sorry ;(

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (�|�)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]