Skip site navigation (1) Skip section navigation (2)

Re: ORDER BY and NULLs

From: Jean-Luc Lachance <jllachan(at)sympatico(dot)ca>
To: terry(at)ashtonwoodshomes(dot)com
Cc: mailreg(at)numerixtechnology(dot)de, pgsql-sql(at)postgresql(dot)org
Subject: Re: ORDER BY and NULLs
Date: 2004-09-19 16:19:55
Message-ID: 414DB1AB.5080207@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-sql
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
> 

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-09-19 16:25:00
Subject: Re: How to check postgres running or not ?
Previous:From: Jeff EckermannDate: 2004-09-19 16:08:18
Subject: Re: How to check postgres running or not ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group