From: speedboy <speedboy@nomicrosoft.org>
To: pgsql-sql@postgresql.org
Subject: Ordering by field using lower()
Date: Sun, 14 Oct 2001 13:45:02 +1000 (EST)
Message-ID: <Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com> (raw)
Hi, please view the output from some queries below. My question is, why is
the ordering apparently wrong when using the lower() function?
The field "pid" is of type int2 and there is no need to use lower(), but I
will need to put a test clause in php if my order type is pid so I do not
use lower() in the query. Which is not a problem but I am interested as to
why lower() returns a different set of results when being used on int2.
Thanks.
logs=# select max(pid) from syslogd;
max
-------
31924
(1 row)
logs=# select min(pid) from syslogd;
min
-----
548
(1 row)
logs=# select pid from syslogd order by pid asc limit 10;
pid
------
548
548
548
548
548
876
876
3983
3983
3983
(10 rows)
logs=# select pid from syslogd order by lower(pid) asc limit 10;
pid
-------
25272
25389
26170
26323
29958
30294
30294
30504
30504
31770
(10 rows)
logs=# select pid from syslogd order by pid desc limit 10;
pid
-------
31924
31924
31924
31924
31924
31923
31923
31923
31923
31922
(10 rows)
logs=# select pid from syslogd order by lower(pid) desc limit 10;
pid
------
9088
9088
876
876
548
548
548
548
548
5158
(10 rows)
logs=#
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: speedboy@nomicrosoft.org
Subject: Re: Ordering by field using lower()
In-Reply-To: <Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com>
References: <Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox