Re: ORDER BY and NULLs

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(at)ashtonwoodshomes(dot)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(at)greatgulfhomes(dot)com
> Fax: (416) 441-9085
>
>
>
>>-----Original Message-----
>>From: pgsql-sql-owner(at)postgresql(dot)org
>>[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of T E Schmitz
>>Sent: Sunday, September 19, 2004 10:58 AM
>>To: pgsql-sql(at)postgresql(dot)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
>