From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Funny date-sorting task
Date: Sun, 13 May 2007 13:47:20 -0000
Message-ID: <b480e4aa32acfdc5172399e72249e00a@biglumber.com> (raw)
In-Reply-To: <4646508B.5050404@gmx.net>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> Every task has a timestamp X that may be NULL or a date.
> ...
> 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
Assuming you mean the literal sense of "today", and that future
dates show with the least furthest away first:
SELECT * FROM yourtable
ORDER BY
CASE
WHEN X::date = now()::date THEN 1
WHEN X::date < now()::date THEN 2
WHEN X IS NULL THEN 3
ELSE 4
END,
CASE
WHEN X::date-now()::date < 0 THEN now()-X
ELSE X-now()
END;
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200705130942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGRxZIvJuQZxSWSsgRAwrsAJ9HzZXzf3sQs0FVNSrhxN5UpGhc+wCcDygQ
obe5G3b58+pXhqy4Ybh/OM8=
=rJpn
-----END PGP SIGNATURE-----
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@turnstep.com
Subject: Re: Funny date-sorting task
In-Reply-To: <b480e4aa32acfdc5172399e72249e00a@biglumber.com>
* 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