Message-ID: <414DB1AB.5080207@sympatico.ca>
Date: Sun, 19 Sep 2004 12:19:55 -0400
From: Jean-Luc Lachance <jllachan@sympatico.ca>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
MIME-Version: 1.0
To: terry@ashtonwoodshomes.com
Cc: mailreg@numerixtechnology.de, pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
References: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
In-Reply-To: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

select ... order by "FROM" is not null, "FROM";

If you have large amount of rows (with or without nulls) it is faster if 
use a partial index.

create index ... on ...("FROM");
create index ... on ...("FROM") where "FROM" is null;


JLL


terry@ashtonwoodshomes.com wrote:

> Use the coalesce() function.  (coalesce returns the first non-null value in its list)
> 
> Specifically
> 
> ORDER BY coalesce("TO", 0), "FROM"
> 
> If you have records in "TO" column whose values is LESS then 0, then you need to replace 0 with
> something that sorts BEFORE the first most value that your TO result can return.
> 
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
> Fax: (416) 441-9085
> 
> 
> 
>>-----Original Message-----
>>From: pgsql-sql-owner@postgresql.org
>>[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of T E Schmitz
>>Sent: Sunday, September 19, 2004 10:58 AM
>>To: pgsql-sql@postgresql.org
>>Subject: [SQL] ORDER BY and NULLs
>>
>>
>>Hello,
>>
>>I am using PostgreSQL 7.4.2 and as I understand NULL values
>>always sort
>>last.
>>
>>However, I have a table from which select using two numerical
>>sort keys
>>"FROM" and "TO". "TO" might be NULL and I would like to display those
>>rows first (without sorting the column in descending order).
>>
>>Is there any way this can be achieved without inserting bogus values
>>into that column?
>>
>>--
>>
>>
>>Regards/Gruß,
>>
>>Tarlika Elisabeth Schmitz
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
>