X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8F25954F7B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Feb 2005 19:55:06 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 14197-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Feb 2005 19:54:57 +0000 (GMT)
Received: from window.monsterlabs.com (window.monsterlabs.com
	[216.183.105.176])
	by svr1.postgresql.org (Postfix) with SMTP id E1F1A53C92
	for <pgsql-sql@postgresql.org>; Wed, 23 Feb 2005 19:54:56 +0000 (GMT)
Received: (qmail 22019 invoked from network); 23 Feb 2005 19:54:56 -0000
Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?)
	(209.149.56.238) by 0 with SMTP; 23 Feb 2005 19:54:56 -0000
In-Reply-To: <200502160939.27431.andreak@officenet.no>
References: <200502160055.59288.andreak@officenet.no>
	<20050216044735.GA2343@wolff.to>
	<200502160939.27431.andreak@officenet.no>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <b27f2f6549c676ab0afe3b615e905230@sitening.com>
Content-Transfer-Encoding: 7bit
Cc: Bruno Wolff III <bruno@wolff.to>
From: Thomas F.O'Connell <tfo@sitening.com>
Subject: Re: Making NULL entries appear first when ORDER BY <field> ASC
Date: Wed, 23 Feb 2005 13:54:50 -0600
To: PgSQL - SQL <pgsql-sql@postgresql.org>
X-Mailer: Apple Mail (2.619.2)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200502/279
X-Sequence-Number: 20389

How would one know from the reference material that it is possible to 
include IS NOT NULL in an ORDER BY clause?

Similarly, other than the FAQ, I've never been able to tell from the 
SELECT documentation why ORDER BY random() works.

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Feb 16, 2005, at 3:39 AM, Andreas Joseph Krogh wrote:

> On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote:
>>> Now, as you see, touples with NULL in the "start_time"-field appear
>>> "after" the others. I would like to make all entries where 
>>> start_time IS
>>> NULL apear *before* all the others. Any idea how to achieve this?
>>
>> SELECT start_date, start_time, end_time, title
>> FROM onp_crm_activity_log
>> WHERE start_date IS NOT NULL
>> ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC;
>>
>> This assumes you want the NULL start times first within a particular
>> date. Otherwise change the order in the ORDER BY clause.
>
> Thanks! This si, IMO, the cleanest solution as it doesn't involve any
> COALESCE.