From: | Rodrigo De León <rdeleonp(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Funny date-sorting task |
Date: | 2007-05-13 08:49:24 |
Message-ID: | a55915760705130149n58e6447av1c321840f98a8d9d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 5/12/07, Frank Bax <fbax(at)sympatico(dot)ca> wrote:
> 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
Less verbose:
ORDER BY CASE
WHEN x = today THEN 1
WHEN x < today THEN 2
WHEN x IS NULL THEN 3
WHEN x > today THEN 4
ELSE 5
END
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2007-05-13 13:47:20 | Re: Funny date-sorting task |
Previous Message | Frank Bax | 2007-05-13 03:13:50 | Re: Funny date-sorting task |