Re: [SQL] How do you compare (NULL) and (non-NULL)?
Bruno Wolff III wrote: On Tue, Oct 26, 2004 at 16:23:20 -0400, Wei Weng [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How do you compare (NULL) and (non-NULL)?
On Fri, Oct 29, 2004 at 11:59:15 -0400, Wei Weng [EMAIL PROTECTED] wrote: How do I write that? SELECT Parent FROM Channels ORDER BY Parent IS NULL, Parent ASC; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How do you compare (NULL) and (non-NULL)?
On Tue, Oct 26, 2004 at 16:23:20 -0400, Wei Weng [EMAIL PROTECTED] 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
[SQL] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How do you compare (NULL) and (non-NULL)?
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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How do you compare (NULL) and (non-NULL)?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
