Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 908FD9FB2DE
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 13 May 2007 00:14:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 03554-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun, 13 May 2007 00:13:57 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from tomts20-srv.bellnexxia.net (tomts20.bellnexxia.net
	[209.226.175.74])
	by postgresql.org (Postfix) with ESMTP id 5986B9FB2DC
	for <pgsql-sql@postgresql.org>; Sun, 13 May 2007 00:13:57 -0300 (ADT)
Received: from acer.sympatico.ca ([65.95.160.157])
	by tomts20-srv.bellnexxia.net
	(InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id
	<20070513031355.QSSZ1637.tomts20-srv.bellnexxia.net@acer.sympatico.ca>
	for <pgsql-sql@postgresql.org>; Sat, 12 May 2007 23:13:55 -0400
Message-Id: <5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca>
X-Sender: b1wudu74@pop6.sympatico.ca
X-Mailer: QUALCOMM Windows Eudora Version 5.2.1
Date: Sat, 12 May 2007 23:13:50 -0400
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: Funny date-sorting task
In-Reply-To: <4646508B.5050404@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/96
X-Sequence-Number: 28376

At 07:40 PM 5/12/07, Andreas wrote:
>I've got a stack of tasks to show in a list.
>Every task has a timestamp  X  that may be NULL or a date. It contains the 
>date when this tasks should be done.
>Sometimes it has date and the time-part, too.
>
>
>The list should be like this:
>1)   X  sometime today should come first in ascending time order.
>2)   X  in the past should show up after (1) in descending order so that 
>not so long back dates come first
>3)   X  =  NULL
>4)   X sometime in the future
>
>Could you provide a clever solution?


ORDER BY CASE WHEN X=today THEN 1 ELSE
CASE WHEN X<today THEN 2 ELSE
CASE WHEN X IS NULL THEN 3 ELSE
CASE WHEN X>today THEN 4 ELSE 5 END END END END