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

Re: Funny date-sorting task

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Funny date-sorting task
Date: 2007-05-13 03:13:50
Message-ID: 5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: Rodrigo De LeónDate: 2007-05-13 08:49:24
Subject: Re: Funny date-sorting task
Previous:From: AndreasDate: 2007-05-12 23:40:59
Subject: Funny date-sorting task

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