Ordering by field using lower()

From: speedboy <speedboy(at)nomicrosoft(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Ordering by field using lower()
Date: 2001-10-14 03:45:02
Message-ID: Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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=#

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message guard 2001-10-14 04:07:41 to_char()??
Previous Message Lee Harr 2001-10-13 21:51:22 Re: GROUPING