From: | "Greg Sabino Mullane" <greg(at)turnstep(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Funny date-sorting task |
Date: | 2007-05-13 13:47:20 |
Message-ID: | b480e4aa32acfdc5172399e72249e00a@biglumber.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
-----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(at)turnstep(dot)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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Rodrigo De León | 2007-05-14 05:56:23 | Re: table juxtaposition |
Previous Message | Rodrigo De León | 2007-05-13 08:49:24 | Re: Funny date-sorting task |