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 817B954F98
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Feb 2005 20:12:46 +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 19974-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Feb 2005 20:12:42 +0000 (GMT)
Received: from www.onnet.cc (unknown [204.31.68.132])
	by svr1.postgresql.org (Postfix) with ESMTP id 5C3B354E34
	for <pgsql-sql@postgresql.org>; Wed, 23 Feb 2005 20:12:40 +0000 (GMT)
Received: from 192.168.0.3 by www.onnet.cc (Sambar SMTPD);
	id s20050223131239.472; Wed, 23 Feb 2005 13:12:39
Message-ID: <421CE3B7.1030705@kensystem.com>
Date: Wed, 23 Feb 2005 13:12:39 -0700
From: Ken Johanson <pg-user@kensystem.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20050131)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Thomas F.O'Connell" <tfo@sitening.com>
Cc: PgSQL - SQL <pgsql-sql@postgresql.org>,
	Bruno Wolff III <bruno@wolff.to>
Subject: Re: Making NULL entries appear first when ORDER BY <field>
References: <200502160055.59288.andreak@officenet.no>
	<20050216044735.GA2343@wolff.to>
	<200502160939.27431.andreak@officenet.no>
	<b27f2f6549c676ab0afe3b615e905230@sitening.com>
In-Reply-To: <b27f2f6549c676ab0afe3b615e905230@sitening.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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/280
X-Sequence-Number: 20390

Well, for the docs to list every possible conditional-statement for an 
order by clause would just about include them all, so be all the more 
confusing. Sub queries, IN, aggregate functions, aliases.. the list goes 
on and on. I'd say that knowledge (that most conditionals can be used in 
an order-by or group-by) should be implicit once a person has a basic 
understanding of the language.

Thomas F.O'Connell wrote:

> 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>