Message-ID: <414DB594.5000300@numerixtechnology.de>
Date: Sun, 19 Sep 2004 17:36:36 +0100
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
MIME-Version: 1.0
To: Jean-Luc Lachance <jllachan@sympatico.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
References: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
	<414DB1AB.5080207@sympatico.ca>
In-Reply-To: <414DB1AB.5080207@sympatico.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hello Jean-Luc,
You must've been reading my mind. I was just wondering what to do about 
indexing on that particular table. I read somewhere that an Index is not 
going to improve the performance of an ORDER BY if the sort column 
contains NULLs because NULLs aren't indexed?

For the sake of the example I had simplified matters a wee bit. What I 
really have is:

SELECT * FROM PRODUCT ORDER BY NAME, FROM, TO, FROM2, TO2

FROM, TO, FROM2, TO2 might be NULL. If FROM is NULL, TO will be NULL. If 
FROM2 is NULL, TO2 will be NULL.

How would you index this table?

Kind regards,
Tarlika

Jean-Luc Lachance wrote:

> 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