pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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=# 




view thread (2+ messages)  latest in thread

Message-ID: <Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com>
Permalink:  ../Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.21.0110141341180.9979-100000@bedrock.evark.com

 · 

reply

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