Thread: How do you compare (NULL) and (non-NULL)?
In the following query SELECT Parent FROM Channels ORDER BY Parent ASC; If I have a couple of (NULL)s in the field [Parent], they will be listed at the bottom of the query result. Is it because PostgreSQL considers (NULL) as the biggest value? If I run the same query under MSSQL Server 2000, I get the exact opposite result regarding the order of (NULL)s and (non-NULL) values. They are listed at the very beginning of the query result. Thanks Wei
On Tue, 26 Oct 2004, Wei Weng wrote: > In the following query > > SELECT Parent FROM Channels ORDER BY Parent ASC; > > If I have a couple of (NULL)s in the field [Parent], they will be listed at > the bottom of the query result. > > Is it because PostgreSQL considers (NULL) as the biggest value? If I run the > same query under MSSQL Server 2000, I get the exact opposite result > regarding the order of (NULL)s and (non-NULL) values. They are listed at the > very beginning of the query result. The spec basically says (IIRC) that implementations must either treat all nulls as greater than all non-nulls for ordering or less than all non-nulls for ordering, but that different implementations may choose different choices. I think the most recent version (at least) provides an option to specify which way to handle nulls, but we don't support that as far as I know.
On Tue, Oct 26, 2004 at 01:48:48PM -0700, Stephan Szabo wrote: > On Tue, 26 Oct 2004, Wei Weng wrote: > > > In the following query > > > > SELECT Parent FROM Channels ORDER BY Parent ASC; > > > > If I have a couple of (NULL)s in the field [Parent], they will be listed at > > the bottom of the query result. > > > > Is it because PostgreSQL considers (NULL) as the biggest value? If I run the > > same query under MSSQL Server 2000, I get the exact opposite result > > regarding the order of (NULL)s and (non-NULL) values. They are listed at the > > very beginning of the query result. you could try to use COALESCE to treat NULLs as either a minimal or maximal value so that your ordering is correct : SELECT Parent FROM Channels ORDER BY COALESCE(Parent, -1) ASC; to treat NULLs as -1 for example hth Jerome Alet
On Tue, Oct 26, 2004 at 16:23:20 -0400, Wei Weng <wweng@kencast.com> wrote: > In the following query > > SELECT Parent FROM Channels ORDER BY Parent ASC; > > If I have a couple of (NULL)s in the field [Parent], they will be listed at > the bottom of the query result. > > Is it because PostgreSQL considers (NULL) as the biggest value? If I run > the same query under MSSQL Server 2000, I get the exact opposite result > regarding the order of (NULL)s and (non-NULL) values. They are listed at > the very beginning of the query result. If the order matters, you can order by IS NULL or IS NOT NULL.
Bruno Wolff III wrote: > On Tue, Oct 26, 2004 at 16:23:20 -0400, > Wei Weng <wweng@kencast.com> wrote: > >>In the following query >> >>SELECT Parent FROM Channels ORDER BY Parent ASC; >> >>If I have a couple of (NULL)s in the field [Parent], they will be listed at >>the bottom of the query result. >> >>Is it because PostgreSQL considers (NULL) as the biggest value? If I run >>the same query under MSSQL Server 2000, I get the exact opposite result >>regarding the order of (NULL)s and (non-NULL) values. They are listed at >>the very beginning of the query result. > > > If the order matters, you can order by IS NULL or IS NOT NULL. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > How do I write that? Thanks Wei
On Fri, Oct 29, 2004 at 11:59:15 -0400, Wei Weng <wweng@kencast.com> wrote: > > How do I write that? SELECT Parent FROM Channels ORDER BY Parent IS NULL, Parent ASC;