X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 205FF4794DA
	for <pgsql-sql@postgresql.org>; Wed,  5 Mar 2003 04:47:12 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 82536-10 for <pgsql-sql@postgresql.org>;
	Wed,  5 Mar 2003 04:47:09 -0500 (EST)
Received: from jester.inquent.com (unknown [216.208.117.7])
	by postgresql.org (Postfix) with ESMTP id 205A5479E09
	for <pgsql-sql@postgresql.org>; Tue,  4 Mar 2003 17:01:26 -0500 (EST)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by jester.inquent.com (8.12.6/8.12.6) with ESMTP id h24M1xJs016799;
	Tue, 4 Mar 2003 17:02:00 -0500 (EST) (envelope-from rbt@rbt.ca)
Subject: Re: Sorting by NULL values
From: Rod Taylor <rbt@rbt.ca>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Ian Burrell <ib@onsitetech.com>, pgsql-sql@postgresql.org
In-Reply-To: <20030304120713.W55865-100000@megazone23.bigpanda.com>
References: <20030304120713.W55865-100000@megazone23.bigpanda.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-irRRNKQ3Bfh0iSBeZULi"
Organization: 
Message-Id: <1046815319.15229.87.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 
Date: 04 Mar 2003 17:01:59 -0500
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/144
X-Sequence-Number: 12184

--=-irRRNKQ3Bfh0iSBeZULi
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-03-04 at 15:13, Stephan Szabo wrote:
> On Tue, 4 Mar 2003, Ian Burrell wrote:
>=20
> > I am doing a query where I need to sort by a column that may be NULL
> > because it is coming from an OUTER JOIN.  I noticed a difference between
> > PostgreSQL and other databases about where NULLs show up.  It seems that
> > with Postgres, NULLs are sorted after other values.  Other databases
> > sort them before.
>=20
> > Is there any standard on how sorting NULLs work?  Is there a way to

If you care, order by their boolean equivelent first:

order by field is null desc, field

DESC puts nulls first, since true > false

> IIRC, they're either considered greater than or less than non-NULL values,
> but the decision is up to the implementation.
>=20
> > change Postgres's behavior?  Is there a way to replace the NULLs with
> > empty strings?
>=20
> Coalesce should work.
>=20
>=20
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-irRRNKQ3Bfh0iSBeZULi
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+ZSJX6DETLow6vwwRAiKIAJ9oW/78RrCuqjYpsBUWc7I+9q9I/ACfaehQ
q6DGzM9UTbZppgfZm81PAvs=
=FG9k
-----END PGP SIGNATURE-----

--=-irRRNKQ3Bfh0iSBeZULi--