pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Funny date-sorting task
Date: Sat, 12 May 2007 23:13:50 -0400
Message-ID: <5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca> (raw)
In-Reply-To: <4646508B.5050404@gmx.net>

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




view thread (4+ messages)  latest in thread

Message-ID: <5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca>
Permalink:  ../5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca/
Also on:    postgresql.org/message-id/5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: fbax@sympatico.ca
  Subject: Re: Funny date-sorting task
  In-Reply-To: <5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca>
  References: <4646508B.5050404@gmx.net> <5.2.1.1.0.20070512231137.042f88d0@pop6.sympatico.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox