From: Jean-Luc Lachance <jllachan@sympatico.ca>
To: terry@ashtonwoodshomes.com
Cc: mailreg@numerixtechnology.de
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
Date: Sun, 19 Sep 2004 12:19:55 -0400
Message-ID: <414DB1AB.5080207@sympatico.ca> (raw)
In-Reply-To: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
References: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
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
>
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: jllachan@sympatico.ca, terry@ashtonwoodshomes.com, mailreg@numerixtechnology.de
Subject: Re: ORDER BY and NULLs
In-Reply-To: <414DB1AB.5080207@sympatico.ca>
* 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