Thread: 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!
Your query doesn't appear to include an "order by" clause. The=20=20 results won't be sorted without an "order by". On May 3, 2007, at 4:12 PM, Jose Blanco wrote: > I=92m using version 7.3 of postgres and when I issue the following=20=20 > command > > > > select author, sort_author from itemsbyauthor where sort_author=20=20 > 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=92s are not grouped together when I think they=20=20 > should be. Is this something that is fixed in a more current=20=20 > 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) ]