From: T E Schmitz <mailreg@numerixtechnology.de>
To: Jean-Luc Lachance <jllachan@sympatico.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
Date: Sun, 19 Sep 2004 17:36:36 +0100
Message-ID: <414DB594.5000300@numerixtechnology.de> (raw)
In-Reply-To: <414DB1AB.5080207@sympatico.ca>
References: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
<414DB1AB.5080207@sympatico.ca>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de, jllachan@sympatico.ca
Subject: Re: ORDER BY and NULLs
In-Reply-To: <414DB594.5000300@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox